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) 
  

);

123