Composite Primary Key or Single Primary Key

Question

Is it better to have a single primary key, or use composite primary keys (usually, they are combination of one primary key and foriegn keys). I have examples below:

Composite Primary Key example:

AMeta

    --- AMetaId - Primary Key
    --- AMetaText

BMeta

    --- BMetaId - Primary Key
    --- AMetaID - Foreign Key to Table AMeta
    --- BMetaText

A

    --- AId - Primary Key
    --- AMetaId - Primary Key and Foreign Key to Table AMeta
    --- AText

B

    --- BId - Primary Key
    --- BMetaId - Primary Key Foreign Key to Table BMeta
    --- AId - Primary Key and Foreign Key to Table A
    --- BText


Single Primary Key example:

AMeta

    --- AMetaId - Primary Key
    --- AMetaText

BMeta

    --- BMetaId - Primary Key
    --- AMetaId - Foreign Key to Table AMeta
    --- BMetaText

A

    --- AId - Primary Key
    --- AMetaId - Foreign Key to Table AMeta
    --- AText

B

    --- BId - Primary Key
    --- BMetaId - Foreign Key to Table BMeta
    --- AId - Foreign Key to Table A
    --- BText

Which is the better Database Design?

Answer 1

The first scheme makes no sense, because it implies (and allows) that there can be multiple rows in Table B with the same BId value but with different values of AId, and there is no meaning associated with column Bid. Is it a FK to somewhere else? If so, to what? If not, what is generating it ? What does it mean?

The second scheme, on the other hand, is logically consistent, but implies that rows in Table B can be associated with two different rows in Table AMeta,

1. through Table B using the FK Column BMetaId and from there to Table AMeta using TableB.AMetaId, and
2. Through table BMeta using column BMetaId to Table BMeta and from there to AMeta using BMEta.AMetaId

Is this really an accurate representation of your business domain model?

Answer 2

I genereally tend to use single-column primary keys almost exclusively - either there is a good natural key available (pretty rarely), or then I add a surrogate INT IDENTITY key to the table.

My main reasons are:

• the primary key needs to be unique, non-null at all times
• the primary key is the clustering key in SQL Server by default, which adds stable (non-changing), narrow (4 bytes max.) and preferably ever-increasing to the list of criteria
• with a single column primary key, all my foreign keys are also easy to use single columns - I don't like having to join two tables on 3, 5 or even more columns just to get the join to work, which is exactly what happens if you have a compound primary key