Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gregoryagu
Yak Posting Veteran
80 Posts |
 Posted - 2011-11-12 : 08:51:30
|
| I have a table, PhoneNumbers, which can related back to two different parent tables, Contacts and Companies.In other words, a phone number can relate to either a Contact or a Company.What would be the best method to model this relation?What I have been doing is have a field called EntityId on the PhoneNumber table, and then two foreign keys on the PhoneNumber table, one going to the Contacts table, and the other going back to the Companies table using the same field.But is there a better way to model this?Greg |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-12 : 10:07:11
|
| Why not have a PhoneNumberID on the Contacts and Companies table.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
gregoryagu
Yak Posting Veteran
80 Posts |
Posted - 2011-11-12 : 11:10:54
|
| Because their can be many phone numbers, not just one. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-12 : 12:00:17
|
| In that casecompanyContactPhoneNumberCompanyPhonenumber (CompanyID, PhonenumberID)ContactPhonenumber (ContactID, PhonenumberID)orcompanyContactPhoneNumberPhonenumberLink (Type, EntityID, PhonenumberID) Type in C0ntactID, CompanyIDFirst one is probably easiest to deal with but they are equivalent.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
How To Relate Two One To Many Relations