DB Design Tips

Don't use business related field as primary key

• business related fields examples: individual tax number, national company number, etc
• it's not a good idea:
  • some business related field data may change
  • don't have control over this field (format may change in future)
• solution:
  • use a "id" like "surrogate key", ex: id, customer_id, etc.

Don't store redundant data

• don't store redundant data, for example: storing both DoB and age (age can be calculated based on DoB)
• it's not a good idea:
  • extra resource to sync data

Use data integrity methods:

• primary key, foreign key, unique key, not null, check

  CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT,
    CHECK (Age >= 18) 
);

Do not store optional types of data in different columns

• ex: different types of phone number
• solution: store phone type and phone number, in separate columns, or in a separate table and using one to many relationship

Do not use the wrong data types and sizes

• text, number, date

123