The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Naming convention for surrogate keys

I am designing a data warehouse and trying to figure out a naming convention for surrogate keys.

So, when the OLTP database has a table like Employees that has a primary key called EmployeeID, I would like to add a surrogate key that is generated by the data warehouse database. What should I name that surrogate key field ?

What do you do ? Any other advice about naming in the data warehouse is also welcome.
Craig HB Send private email
Thursday, December 28, 2006
 
 
>> What do you do?

Avoid using surrogate keys in data warehouses, for one thing.

http://discuss.joelonsoftware.com/default.asp?design.4.431299.4
David Aldridge Send private email
Thursday, December 28, 2006
 
 
If you already have a key called EmployeeID which contais a value that will never e changed, then why on earth do you think you need an additional surrogate key?
Tony Marston Send private email
Friday, December 29, 2006
 
 
This why I need a surrogate key:

If the employees table is a slowly changing dimension (SCD) and I want to use the Type 2 approach, I'll need to use a surrogate key.

This is what I am basing that on:
http://www.ralphkimball.com/html/designtipsPDF/DesignTips2000%20/KimballDT8Perfectly.pdf

Ok. I realise the Employees table is not the best example for a SCD table. Make it a Products table, where the price changes over time. If I need to add a surrogate ID for the Products table, what naming convention should I use ?
Craig HB Send private email
Friday, December 29, 2006
 
 
For example, Oracle Designer has following default conventions:
 - table name is plural: PRODUCTS
 - surrogat primary key is table name (singular) + "_ID", thus key name is : PRODUCT_ID

Not to be taken as supreme law, just example.
moronica
Monday, January 01, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz