SQL Server - many to many join

Asked By user

10-May-07 11:41 PM

Can one add some extra data into the link table on a many to many join.
Currently it only contains two id's from each table as primary key.

Have a group table and a member table and a link table. Want to add a
'title' column but the only current table it could go in would be the
link table. Ok, or no?

TIA

10-May-07 11:58 PM

Yes, if the attribute belongs to the link, and not either of the tables it
is linking, then the correct way to handle it is to add the attribute to the
link table.  So if a person can be President of one group, and Treasurer of
another group, then title would go in the link table.

Tom

11-May-07 02:05 AM

Don't obsess over sample design recipes and don't get mislead by
jargon. There is only one type of table. Some people find labels like
are just generalised lables, they are not design rules. Normalization
is the design rule that should tell you which table your data belongs
in.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

11-May-07 11:15 AM

I think I understand what you are saying. I am not obsessing but trying
to use common nomenclature to ask for some educated opinions. I took
normalization into consideration but still could not determine if my
thought was correct.

I have since detemined that an addition table will be required because
in fact members can have more than one title.

Thanks

11-May-07 11:17 AM

Tom,
Thanks

11-May-07 11:22 AM

Boyce-Codd Normal Form. If the key of your "link table" is the
determinant for Title then Title belongs in that table. If not, it
doesn't.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--