CODEWITHSUNDEEP
databasedatabase-designrelational-database
noobcoder
noobcoder

Reputation: 160

One-to-many relationship: composite primary key or single primary key?

I have a table person containing personal info, and I have another table person_contact to store contact information about that person (type shows if it's a phone record or email record, and record contains the actual phone number or email address).

I have designed the schema like this: enter image description here

In person_contact I have declared pcont_id and person_id as PK while person_id is a FK referencing person.person_id. Do I need the PK pcont_id at all? When should I use a single PK in a one-to-many relationship and when is it better to use composite PK?

Upvotes: 2

Views: 3070

Answers (1)

PSK
PSK

Reputation: 17943

You don't need person_id as a part of your primary key in person_contact table. pcont_id should be the primary key if you have a one to many relationship between the two tables.

Do I need the PK pcont_id at all?

I suggest, it should be there and should be primary key of your table assuming you can have multiple contacts for one person.

If one person can have only one contact, in that case you don't need that table itself, you can store the data directly in the person table.

If you still want to store it separately then you don't need pcont_id column, your person_id column should be marked as primary key.

When should I use a single PK in a one-to-many relationship and when is it better to use composite PK?

Composite primary key is used when you have a junction table/associative table to map a many-to-many relationship. In case of one-to-many relationship you don't need composite primary key with the foreign key column.

Upvotes: 2

Related Questions