Composite primary keys in N-M relation or not?
- by BerggreenDK
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, thats 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.
