Transcript Designing Your Primary Keys

Designing Your
Primary Keys
Denise Dykstra
DAMA - Minnesota
November 20, 2002
DAMA - MN 11/20/2002
Isn’t It
Inherently Obvious?



DAMA - MN 11/20/2002
Deliberate design
A variety of options
Pros and Cons
Primary Key Rules





DAMA - MN 11/20/2002
Uniquely Identify an object
Ability to Access one object
Link to another table (FK)
No Changes (updates)
Control over Value
Considerations



Prevailing Practice at company
Conceptual vs. Surrogate
Control over value
 Business
supplies
 Database control
 Programmatic control
 Insert method


DAMA - MN 11/20/2002
Discrete vs. Non-discrete
Scope of uniqueness
Options


Conceptual 3NF
Single column PK for every table
 Surrogate
 Generated




DAMA - MN 11/20/2002
Homemade sequence or random
number
Intelligent key
Record locator (OID)
High / Low value
Surrogate Key Issues







DAMA - MN 11/20/2002
Will it ever have “meaning”?
How is the value determined?
Partitioning ability?
Updates to data: good or bad?
Meaningless to users
Complicates the index strategy
RI could contain both meaningless
key and candidate key
Consider this….
A primary key strategy using all
meaningless (surrogate,
generated) keys can lead to
PERFECT referential database
integrity, while the data itself is
meaningless.
DAMA - MN 11/20/2002
Using Tools



DAMA - MN 11/20/2002
Logical models should directly
support business rules
Physical models reflect the
database design decisions
Problem is traceability
Database Platforms





DAMA - MN 11/20/2002
Oracle
IBM and DB2 flavors
Sybase
SQL Server
Others?
It Depends!


DAMA - MN 11/20/2002
Understanding various methods
helps improve your product,
whether it is a data model or a
database design.
Use knowledge to achieve project
objectives: speed, accuracy,
abstraction
Questions?
Denise Dykstra
VP Programs - DAMA Iowa
[email protected]
(515) 994 - 2297
DAMA - MN 11/20/2002