As a data modeling expert in your company, you are frequently being asked modeling advice. On your way to the coffee machine one day, a project team manager stops you and explains: “Our team is working hard to document business rules, yet we feel we are lacking a number of IT rules. For example, what are the rules around when we should use a surrogate key? Some members of my team want to have a surrogate key on every table, whereas other members want to only use the natural keys. What rules should our team apply in deciding when to use a surrogate key?”
How would you respond? In the text box below, write the rule or rules you follow in deciding when to use a surrogate key. If there are a number of conditions that must be met for you to use a surrogate key, feel free to document these rules in an in if-then-else format (e.g. “If a natural key exists, and if it does not change over time,…”).