Junction Tables

Last Post 15 Jan 2007 12:43 PM by prospec. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
lsomers
New Member
New Member

--
11 Jan 2007 11:49 AM
I have two tables, Members and Services, that have a many-to-many relationship. Each Member can have zero or more Services and each Service can have zero or more Members. So I have created a Junction Table, Member_X_Service.

I need to record the date each member enrolled in and stopped a Service. So it seems I should add these as two columns in my junction table, StartDate and EndDate. My collegue tells me a junction table should only have the PK's of the two Parent Tables and not any additional columns.

What do you think?
lsomers
New Member
New Member

--
11 Jan 2007 12:40 PM
Thanks, so much, for the reply.

Yes, EndDate would be null initially.

Actually there are a number of columns I need for this table (or some ancillary table) including EffectiveDate, EmployerID (From an Employer Table), Deduction Amount, Physician Code, and others.

You seem to suggest that, if I have a number of nulls (I will), I possibly use a 4th table that contains the member_service_id as a FK. It's not obvious to me what the merit of this is? (It would still have nulls). Doing this would require adding a new PK (member_service-id) to my junction table.

A feature of a Junction Table is the uniqueness of the combination of keys from the two parent tables. In this case, I believe that uniqueness is important. Example, Service 1 is dental. Service 2 is vision. Using the Member_id and Service_id from the two parent tables as the Primary Key in the Junction table guarantees that no member can have to dental services. Adding a new PK to this junction table (member_service_id) prevents this uniqueness from being guaranteed and eliminates this table as a true junction table.

What do you think?
lsomers
New Member
New Member

--
11 Jan 2007 12:57 PM
OK, I get ya! - Thanks for the feeback and advice!!!
SQLUSA
New Member
New Member

--
12 Jan 2007 01:44 PM
You can add non-key columns to a junction table.

However, it appears that the information you are proposing should be in a different table: membership info for the particular service.

Kalman Toth DB, DW & BI Architect, SQL Server Training
URL: http://www.sqlusa.com/order2005grandprix
The Best SQL Server 2005 Training in the World
prospec
New Member
New Member

--
15 Jan 2007 12:43 PM
Just remember that attributes or columns describe the subject or entity. If this is true for your model then mostly likely you should add them to the table in question. As for nulls it should be okay to add them to the table given the meaning of the data and the definition of the Null. Just be aware that nulls could provide inconsistencies when used in calculations.


Acceptable Use Policy
---