Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Enforcing Data Quality while using Surrogate Keys Expand / Collapse
Author
Message
Posted Tuesday, September 8, 2009 12:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 21, 2013 1:51 PM
Points: 6, Visits: 33
Comments posted to this topic are about the item Enforcing Data Quality while using Surrogate Keys
Post #784006
Posted Tuesday, September 8, 2009 12:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 21, 2014 1:49 AM
Points: 238, Visits: 134
It's an interesting take on surrogate keys.

I've designed a few data warehouses over the years and I've not really regarded duplicity as a problem, rather inherent in the data, if it is by nature going to provide historical analysis. Here's why...

- I will often include metadata in the design at the physical level to help understand the lifecycle of the data. These fields include created dates, modified dates, current flag and so on...

- Multiple versions of the same ID would be identified by the "created" and "modified" dates, as well as a flag indicating it is the current version of the record; in essence, this is a slow changing dimension/entity.

- ETL processes check if there is a current record for the incoming data and assign the existing key or create a new one as necessary.

- I *always* move away from intelligent keys for the physical structures; many times I have joined an existing ETL project where the model has relied on the business keys, only to find that the data quality/constraint enforcement causes more problems than it solves.

I'm liking your work, Marvin. I'll keep an eye out for more of your posts.

Cheers
Post #784016
Posted Tuesday, September 8, 2009 3:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 22, 2010 7:54 AM
Points: 6, Visits: 33
"Example 1. (Introduces risk of record duplication):..."

Imo the database should not be making decisions the application should be taking. Whilst your point is technically valid, it's encouraging application logic into the database, where it is difficult to tell a duplicate record from a valid second entry.
Post #784082
Posted Tuesday, September 8, 2009 4:04 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 6:17 AM
Points: 8, Visits: 285
A DWH is a different beast altogether and should not be confused with constructing OLTP database models. Your approach is good for a DWH but less apropraite for an OLTP system, while the author is focusing on OLTP datamodel design and not on DWH design.

Kind Regards,

DM Unseen AKA M. Evers
Post #784103
Posted Tuesday, September 8, 2009 4:09 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 6:17 AM
Points: 8, Visits: 285
Thats exactly what Codd intended when he invented the relational model. He wanted the database to protect the integrity of the data, and hence created the relational model to mathematically prove it could actually be implemented. The contructs around RI are not based on a developers whim or choosing to ignore it, but on Codds profound insight in how to maintain data quality.

Kind Regards,

DM Unseen AKA M. Evers
Post #784104
Posted Tuesday, September 8, 2009 4:19 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 6:17 AM
Points: 8, Visits: 285
Some comments.

The article talks about unique indexes, but formally they are alternake keys or uniquenss constraints.

Everybody should be aware that there are situations in which surrogate keys fail to keep RI working.
IMO RI is an physicalisation step of a data model and should not be initially modeled at all (in eg a logical model). Only when implementing you could add them to tune the database model.


Kind Regards,

DM Unseen AKA M. Evers
Post #784109
Posted Tuesday, September 8, 2009 7:12 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, Visits: 836
Surrogate keys are still the devil.

The problems they prevent pale in comparison to the problems they cause.
That being said, this is far and away the best solution if you are going to use them.



~BOT


Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
Post #784214
Posted Tuesday, September 8, 2009 7:20 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:42 PM
Points: 3,487, Visits: 1,581
I'm a big fan of using surrogate keys when designing databases, but they are not intended to enforce data integrity. They should be used to simplify joins and used solely to establish relationships to other entities in the database.

Example 1 should never happen because any table with a surrogate key should also have a unique constraint placed on the natural key of the table to enforce data integrity.

Example 2 should also never happen because it's not a good practice to use a surrogate key on a linking/intersection table. Those tables should always consist of the combination of the two surrogate keys from the two tables that you are establishing the relationship between.

Thank you for the article. It was definitely a interesting read. I've just never encountered either of the issues that you describe because of the practices that I've listed above.

Greg




Post #784222
Posted Tuesday, September 8, 2009 10:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 3, 2014 11:38 AM
Points: 5, Visits: 52
This article makes it sound like you have to choose between a natural key with a unique index or a surrogate key with a unique index. Every time I use a surrogate primary key I create a unique key on the columns that comprise the natural key. Surrogate and natural keys each have their role and work best when used for the purpose they were intended. Natural keys are the basis of relational database theory and if you do not have them I do not Know why you would use a relational database. Surrogate keys can be used to simplify joins and allow the natural key to change without having to cascade all the dependent foreign keys.

I have to agree that surrogate keys have no role during logical modeling. All entities should have a unique identifier and at the physical level you can add surrogate keys for all tables that have children and multiple column unique identifiers. I have seen "designers" that immediately switch to surrogate keys in the logical modal as soon as the unique identifier goes beyond 2 or 3 attributes without ever identifying the full natural key. It always leads to duplicates and application failure when more than one row is returned. Yes, I agree, your application is perfect and doesn't allow duplicates but users are devious and will always find that odd navigation path that will allow them to insert duplicates!

It is also difficult to find the surrogate key for a given row if you do not know and are not enforcing uniqueness on the natural key. How often to you SELECT * FROM blahblah WHERE surrogate_key_id = 2468? Usually your first query is SELECT surrogate_key_id FROM blahblah WHERE COMPANY_NAME = "ACME" AND LOCATION = 'CINCINATTI' to get the surrogate key to use in subsequent queries. Company name and location are the natural key in BLAHBLAH and should be indexed. Using my method they are indexed as a result of being in a unique key and the surrogate key would also be indexed as the primary key.

As a practical matter, carrying all the unique identifier columns down through all generations can lead to a natural key of 10's of columns. Here is an extreme example using what I believe are natural keys for a person and a department. I propose the natural key of a person is first, middle and last name, suffix, date and time of birth, location of birth, father and mother (who also have the same natural identifier) of the person. In this case the father and mother natural keys are comprised of 8 columns which is 16 columns for two parents plus the other 6 columns for a person makes 22 columns to identify one employee. I propose that the natural key for a department is company name, company location, date incorporated, incorporation location, (natural key for company) department name, department location and date department created. That is 6 columns. Now associate an employee with a department and you have 28 columns in the natural key for the employee department intersection table. You are looking at 34 predicates in your where clause to join these 4 tables.

On the other hand, if you add a surrogate key to the person table (employee and parents) you drop the number of columns in the employee table to 2 surrogate columns for the parents and the 6 others for a total of 8. Now if the company table has a surrogate key the department table drops to 4 columns and if department has a surrogate key the intersaction table now has 2 columns. To join the same 4 tables there are only 3 predicates in the join clause.

I have a question because I am not a Business Rules person. What is this Unification Business Rule? I do not understand the purpose. I searched the web and didn't get any hits in the first few pages. Can anyone give me some references so I can understand?

Post #784399
Posted Tuesday, September 8, 2009 11:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 2, 2013 6:30 AM
Points: 346, Visits: 691
I'm in agreement with G2. Surrogate keys (which my company calls RIDs) are the only way to go for primary keys in entity tables--tables which contain entities. Aside from eliminating the need for cascade updates on primary keys (shudder) RIDs give far higher performance in joins and even RBAR situations. Our RIDs are 4 byte integers but even 8 byte integers would be faster links than long varchar keys.

Tables can also have natural keys--which I've always heard referred to as Alternate Keys. AKs are usually a name of some sort. AKs for us are almost always to enforce a unique constraint, sorting, and the like. Nearly every entity will have both a PK and an AK.

As for intersection tables the only reason I could see to give an intersection table its own surrogate key would be for auditing purposes in a combined audit table. If you don't care about auditing the intersection then the intersection table can (and should) consist soley of two surrogate keys.

I'm somewhat confused by the author's thinking that AKs wouldn't be used as a matter of course in conjunction with surrogate keys. What am I missing?
Post #784439
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse