Thursday, December 29, 2005

ok, another database design task and I found the reference article I had talked about

A few posts ago I mentioned why Primary Keys in a table shouldn't have business meaning. I didn't quote the article I usually re-read for reference. I ran into this article while looking for another subject in www.agiledata.org today.
The section of this article I like the most is:

There are two strategies for assigning keys to tables. The first is to simply use a natural key, one or more existing data attributes that are unique to the business concept. For the Customer table there was two candidate keys, in this case CustomerNumber and SocialSecurityNumber. The second strategy is to introduce a new column to be used as a key. This new column is called a surrogate key, a key that has no business meaning, an example of which is the AddressID column of the Address table... Addresses don’t have an “easy” natural key because you would need to use all of the columns of the Address table to form a key for itself, therefore introducing a surrogate key is a much better option in this case.

The primary advantage of natural keys is that they exist already, you don’t need to introduce a new “unnatural” value to your data schema. However, the primary disadvantage of natural keys is that because they have business meaning it is possible that they may need to change if your business requirement change. For example, if your users decide to make
CustomerNumber alphanumeric instead of numeric then in addition to updating the schema for the Customer table (which is unavoidable) you would have to change every single table where CustomerNumber is used as a foreign key.

If the
Customer table instead used a surrogate key then the change would have been localized to just the Customer table itself (CustomerNumber in this case would just be a non-key column of the table). Naturally, if you needed to make a similar change to your surrogate key strategy, perhaps adding a couple of extra digits to your key values because you’ve run out of values, then you would have the exact same problem.

The fundamental problem is that keys are a significant source of coupling within a relational schema, and as a result they are difficult to change. The implication is that you want to avoid keys with business meaning because business meaning changes.

And the article can be found at Data Modeling 101

Enjoy!


I'm a MCP :-p

Today is my first day at the office after a nice week of vacation. I had such an hectic schedule last month that I spent a lots of time without blogging. The main reason was the time I spent studying for the cert exam 70-305. So yes, I passed the exam.
Advice for those who want to write the exam, try to make as many preparation tests as you can, the exam cram book is a good guide, take the official documentation as a reference, but it shouldn't be the only reference you use. Most of the questions will have slight tiny details on each possible answer, some of them will offer more than two correct answers but you need to select the most efficient solution, so you basically have to train yourself apart from knowing the subject.
Ok, now I'm back to a hectic schedule again but promise to sleep more than 5 hours a day ;)