CODEWITHSUNDEEP
databasedatabase-design
Phil
Phil

Reputation: 1453

Database Design - Working out the primary key for a table

I have a small database (16 entities and about a 70 attributes) but I cant work out the most logical primary key for one of the tables.

I have student and next of kin. A student can have one next of kin but a next of kin can be the NOK for many students. I thought that I should use NOKname and NOKrelationship (relationship to student as a composite key however if for example the NOK has two children they may be the NOK for both and be the father of both!

Attributes of NOK:

NName
NAddress
NTelNumber
NRelationship

Attributes of Student:

StudentNumber
PFirstName
PSurName
PAddress
PDOB
DateRegistered
Sex
PTelNumber
MaritalStatus
DateWaitingList
DatePlaced
ExpectedLengthStay
DateLeave

Thank you for any advice that you can offer!

Upvotes: 0

Views: 207

Answers (2)

pearcewg
pearcewg

Reputation: 9613

For student, I would recommend making a separate key which isn't any of these fields.

It should be a primary key, integer (or bigint if you plan over millions of students), auto-increment field. You can call it something generic like RID (for row identifier).

Next of kin would have a similar primary RID for the table. And then a link table if there are multiple next of kins for a student...if only a single next of kin the NOK table can have a foriegn key RID to the student table RID field.

Upvotes: 0

Walk
Walk

Reputation: 1146

Add an Id to the NOK table and add NOKId to the Student table. This will allow one NOK for each student but the NOK can be for multiple students.

Upvotes: 4

Related Questions