CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-designnormalization
Harper
Harper

Reputation: 1305

Database Design Foreign Keys

Two tables in my database are as follows:

[Employee] Table:
 Id (Primary Key, Autoincrement)
 FirstName
 LastName
 Status

[Status] Table:
 Status (Primary Key)

Status is one of the following: "FullTime" "Contractor" "Terminated"

How should [Employee].Status reference [Status].Status as foreign key? I see two ways of doing this:

  1. [Employee].Status points directly to [Status].Status
  2. I add an Id column to [Status] table and make it PK/Autoincrement. Then [Employee].Status points to [Status].Id. This means I have to do a join in order to get the status text.

Other tables may also reference the Status table. Is one of the two methods the 'correct' way of doing things or are both a matter of design?

Upvotes: 0

Views: 620

Answers (2)

bevacqua
bevacqua

Reputation: 48566

a third option in more complex tables where you want to both delete records and update names without losing reference would be something like

[Employee] Table:
 Id (Primary Key, Autoincrement)
 FirstName
 LastName
 StatusNumber

[Status] Table:
Id (PK)
Number
Name

Upvotes: 0

Andrew Cooper
Andrew Cooper

Reputation: 32596

It's basically a matter of design, but it's generally better to add an ID field to the Status table. This allows you to make changes to the Status values (spelling corrections, language translation, change of term to clarify meaning, etc) without having to update the data in the tables that reference it.

Also, if you link to the string field, then the linking field needs enough space to store the longest status string. Linking to the ID means you just have to store an integer (or at worst, a GUID) in the linking field.

Upvotes: 4

Related Questions