sql server - How can I enforce that a record be a child of only one record when it can be a child of several tables? -

i'm brainstorming how restructure contact information of database. know, phone numbers can linked person (cell phone), family (home phone), organization/business, etc. logically, phone number phone number phone number. there's no real difference between cell phone number , home phone number. , person can have multiple cell phones, family can have multiple phone lines, , organization can have many many phone lines.

normally, when designing tables, means there should single phone number table. , should link in 1 many persons or families or organizations. rub is, how enforce phone record owned single parent record, whether record person record, or family record, or organization record?

the 2 ways i've figured out kludges, in opinion. want elegant solution.

the first create 3 tables, personphones, familyphones , organizationphones. you've got 3 tables mission store same data.

the second create single phone table weird structure. have phone number, nullable field person id, family id , third organization id. add constraint enforces 2 of 3 null.

any ideas?

you can have 3 columns in phonetable (person_id,phone_number, , phone_details). in phone_details column can mention whether cell phone or home or organization. can define unique key on combination of person_id , phone_details.