Friday, December 01, 2006

Choosing Primary Keys

Exert from OracleFAQ

  1. The candidate key must be unique within its domain (the entity it represents, and beyond, if you also intend to
    access external entities).
  2. The candidate key can not hold NULL values (NULL is not zero. Zero is a number. NULL is 'nonexistent value').
  3. The candidate key can never change. It must hold the same value for a given occurrence of an entity for the lifetime of that entity.

BizSutra adds

PrimaryKey has to be HUMAN Readable

There are many strategies for identifying a Primary Key. With advancement of OOP the debate for surrogate keys has elongated. The fact remains that the strength of Object Oriented Programming is readability of the code. This capability has to be extended to persistence layer. Using surrogate keys defeats the above capability. Having Mnemonic code in foreign key fields is a good practice. Gurus suggest is 8.

Data Live Longer Than the Logic Accessing the Data

No comments: