Composite primary keys in N-M relation or not?

views:

answers:

+1 Q:

Composite primary keys in N-M relation or not?

Lets say we have 3 tables (actually I have 2 at the moment, but this example might illustrate the thought better):

[Person]

ID: int, primary key
Name: nvarchar(xx)

[Group]

ID: int, primary key
Name: nvarchar(xx)

[Role]

ID: int, primary key
Name: nvarchar(xx)

[PersonGroupRole]

Person_ID: int, PRIMARY COMPOSITE OR NOT?
Group_ID: int, PRIMARY COMPOSITE OR NOT?
Role_ID: int, PRIMARY COMPOSITE OR NOT?

Should any of the 3 ID's in the relation PersonGroupRole be marked as PRIMARY key or should they all 3 be combined into one composite?? whats the real benefit of doing it or not?

I can join anyways as far as I know, so Person JOIN PersonGroupRole JOIN Group gives me which persons are in which Groups etc.

I will be using LINQ/C#/.NET on top of SQL-express and SQL-server, so if there is any reasons regarding language/SQL that might make the choice more clear, that's the platform I ask about.

Looking forward to see what answers pops up, as I have thought of these primary keys/indexes many times when making combined ones.

EDIT:

Okay, the question was to be misunderstood I can see now.

The question is about, if it makes any sense to mark the three ID's in PersonGroupRole as PRIMARY KEYS for index purpose. Will this add extra speed for joining with each of the three tables, or should they stay without PRIMARY KEY in the PersonGroupRole table and only be Primary in the separate tables.

Sorry, about the confusion. Will try to explain my questions better.

+2 A:

It depends on what the relationships are:

Can a person be in more than one group?
Have multiple roles?
Are role/group 1:1 with multiple people in this combination?
etc

Most likely, you need more tables and 4NF or 5NF to capture this

Example here, but the best link I had is now some crappy link page sorry. Another that describes my questions somewhat

gbn 2010-06-03 18:06:50

Aahh, I see the possible confusion. Sorry, for simplicity, just remove the 3rd tabel then. Just have [Person] [in] [Group]. Every person can be in each group and every group can contain each group. Its not the "data" itself, but more the idea of marking the two foreign ID's as Primary keys or not in the N-M relation. I seem to have forgotten my SQL basics on that one. I am concerned to have too much or too little indexing attached to the "middle relation".

BerggreenDK 2010-06-03 23:48:33

+4 A:

The composite key distinctly identifies each row (assuming that a person can't have the same role in the same group twice), so it makes for a good primary key.

Whether you actually declare that as your physical primary key though depends on the tools that you're using and the rest of the database around these tables. Will you have a lot of other tables that have FKs to this table? If so, then a surrogate key might be in order.

Tom H. 2010-06-03 18:08:41

I'd say it's more complex than that to avoid I/U/D anomalies. What does a surrogate give you too?

gbn 2010-06-03 18:15:28

A surrogate key would simplify joins *IF* there were further tables that had had PersonGroupRole as a parent table. With no surrogate key, you'd have to include all three column in that (new) child table, and make them part of its primary key, combined with whatever new data identifies/requires that child table (such as, perhaps, DateCreated). There are arguments for and against both, and a world of "it depends" based on your specific circumstances to consider before making a final decision.

Philip Kelley 2010-06-03 18:32:36

Okay, clearification: This problem is probarbly much easier than I wrote the question. Sorry about all the fuzz. Each table joins on a "N-M" manner, so one Person be in multiple Groups and have multiple Roles. But lets remove the Roles for the sake of simplicity. I am asking if the "N-M" relation table should have the foreign IDs marked as PRIMARY KEYS (index) or not. Is that good for SQL-server or bad since they are also PRIMARY Keys in their "home table" (eg. Person) - might be a newbie question, but I cant recall or find the best answer.

BerggreenDK 2010-06-03 23:53:06

It is perfectly reasonable to use the PKs from other tables (FKs in the joining table) to comprise a composite PK. In fact, I prefer that method myself.

Tom H. 2010-06-04 13:19:25

ok, thanks I have what I need now. Played a little around with it to see what happend too. Thanks for the trouble.

BerggreenDK 2010-06-04 22:42:56

+1 A:

The question seems to be based on a fundamental misunderstanding about keys. It ought to be clear that making one of those columns a key versus a compound key on all three of them would entirely change the meaning and potential uses of the table. Analysis of business requirements and an understanding of the real world situation that you are modelling ought to determine what columns are unique. Unfortunately the question says nothing about what is actually required of the data model so I think it is unanswerable in any meaningful way.

dportas 2010-06-03 18:49:48

ansaurus

tags:

views:

answers:

Composite primary keys in N-M relation or not?

related questions