[AccessD] Data Warehouse (Was: Primary Key Best Practices)

Charlotte Foust cfoust at infostatsystems.com
Thu Jul 26 13:31:16 CDT 2007


 Can you give an example of a typical dimension table and what you mean
by the source primary table?  I've worked primarily with date dimensions
using the date as the PK.  

Charlotte Foust

-----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 11:04 AM
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




More information about the AccessD mailing list