[AccessD] Primary Key Best Practices

Robert L. Stewart robert at webedb.com
Thu Jul 26 13:04:13 CDT 2007


I have watched all the flaming and was surprised at the lack of
intelligence and total stubborness in some of it.

I do not know if what I do is best practice or not. But, I will
go with consistency.

In a transactional database, all tables have a surrogate, autonumbering
primary key. They also have one or more unique indexes on the candidate
key or keys. Also, indexes on all foreign key columns.

In a data warehouse or data mart, all dimension tables will have a
single column primary key inherited from the source primary table.
All fact tables will have multiple columns that make up the primary
key. There are no snowflakes. It is a true star schema.

Best practice or not, as long as I am consistent in my designs,
I will always know how to use it properly. And, I can always explain
it to the monkeys that come after.

Robert





More information about the AccessD mailing list