Mickey Stuewe hosted T-SQL Tuesday this month. Her topic: data modeling gone wrong. A few choice posts on the topic follow.
Mickey herself looks at a case in which surrogate keys didn’t quite do the trick:
One of the problems I’ve seen with careless use of surrogate keys are the duplication of natural keys. Quite often it’s overlooked that the natural key still needs to have a unique constraint. Without it, the reporting team ends up having to use MAX or DISTINCT to get the latest instance of the natural key, or SSIS packages are needed to clean up the duplicates. This can be compounded with many-to-many tables.
Surrogate keys are not replacements for natural keys; they are physical implementation mechanisms to make your life easier.
Rob Farley wants you to think about design and whether your warehouse is built in a way that helps the business:
Many data professionals look at a data warehouse as a platform for reporting, built according to the available data sources. I disagree with this.
The models within a data warehouse should describe the business. If it doesn’t, it’s a data model gone wrong.
What is the central thing that your business does? What is the main interest point? What do you need to look after? For me, this forms the core of the warehouse.
Thomas Rushton says name your stuff right. Picking the right name can be difficult. “Field1” probably isn’t the right name, though.
Field1 definitely not the right name. Particularly when it’s the field *after* Field3.
Haha. It’s a field naming ring buffer that just happened to reset…