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 q开发者_如何学编程uestions better.

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.

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

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.