February 27, 2004 at 6:59 am
Not sure where to post this, so thanks for bearing with me.
I have a general data structure question (from a newbie)
I have two tables, Client and Phone that connect via a third table housing ClientID and PhoneID. This was done in order to impose RI because there are multiple primary tables connecting to the Phone table. Rather than have multiple columns in the phone table for ClientId, FacilityId, etc. we elected to have the third connecting table.
My question is whether there is a general guideline as to which structure is best? On the one hand, placing multiple FKs in the Phone table is simpler, but incurs a lot of null fields.
February 27, 2004 at 7:50 am
What you are using is called and Associative table. Others call it a Cross Reference table while still others call it an Intersect table.
Creating two tables where the parent table has a primary key and a secondary which has a foreign key for that primary key allows you to create 1-to-1 and 1-to-many relationships. But in our business there is sometimes a need to create a many-to-many relationship.
This can be accomplished by creating two 1-to-many relationships, hence the Associative Table. Each of the main tables has its own primary key. The Associative table contains two foreign keys, one for each of the man tables’ primary keys.
Insulate others from the three tables by creating a view that queries the three tables and provides a many-to-many picture of the data. You can also use this view to update information by creating an INSTEAD OF trigger on the view but that my be over your head right now.
You can look at the Books-Online to read about the INSTEAD OF triggers.
I hope this helps
February 27, 2004 at 9:10 am
Yes, thank you, that answers the question. I understand the concept but the real question is: why are you guys (DBAs) so compulsive about this? <g> It causes extra work to join the tables, so there must be some advantage to it. I don't see that it enhances performance.
SMK
February 27, 2004 at 9:23 am
I can ive you Three POWERFUL Reasons
1.DATA INTEGRITY
2.FLEXIBILITY
3.No Duplication of data
* Noel
February 27, 2004 at 9:59 am
I guess it's hard to argue about that! <g> What if I LIKE duplicate data? (just kidding)
Anyway, thanks for the encouragement to be compulsive.
SMK
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply