[AccessD] Primary Key Best Practices

Robert,

I know nothing about data warehouses.  Would you define fact table and
explain why the PK is a natural key?  I assume that it has to do with speed
but beyond that could you explain the concepts a little?

I am just looking to learn something here about a subject I know nothing
about.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robert L. Stewart
Sent: Thursday, July 26, 2007 2:04 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Primary Key Best Practices

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


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com