SQL for Smarties

[Crito]

I'm surprised he was so polite about it. I expect he'd say that showed a complete lack of understanding of the difference between logical and physical design. Even if you consider it a surrogate key instead of physical locator, it should become apparent when it takes two joins to return an address that using a stateid and cityid (if not a citystateid too) is just a plain bad idea.

[hbroomhall]

Depends on your stance on surrogate keys versus natural keys!

There is a nice comment here, which includes the line:

Besides the fact that Chris Date likes surrogate keys and Joe Celko likes intelligent keys, each alternative has a number of real-world advantages and disadvantages.

which made me smile!

I'm somewhat agnostic on this - I use either where I feel it is appropriate.

Harry.

[dmarsh26]

Personally like most people I expect, I reccomend Surrogate keys unless you have a very specific reason not to. Most databases are utilised by third party applications these days, also businesses tend to change their minds on how they want to operate.

'Natural keys' don't really exist in reality in my mind, you're not born with a social security Id, its a system implementation detail, a busniess process or system redesign could therorectically alter it at any point. Using a Natural key in such cases where in reality changes are unlikey during the lifetime of the system, and there is a lot of data for instance, is an optimisation. Optimisations should only be used when there is a adequate reason to justify them.

Frequently these supposed 'Natural keys' are hangovers from old paper based or mainframe systems, theres very little 'Natural' about them, and its quite possible that say an 'order number' or 'manifest id' could be changed at somepoint during the companies operation. Often these were ways of compressing data into a field, so the ID might have multiple bits of information in it, which is a denormalised design probably not best suited to many of todays systems.

Like many things we are concerned with managing a designing for change, Natural keys form part of the interface to the system so cannot be changed.

"We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil." (Knuth, Donald.)

[Crito]

Some people just never learn... even when their convoluted designs result in a dozen otherwise completely unnecessary joins. Looks at the fields you're retrieving from related tables. Chances are they're the natural keys, because the data is meaningless without them. You can't tell someone they live in stateid 14 and cityid 22, especially if they maintain their own cities and states tables with their own autonumbers.

It's worth pointing out, however, that the rules for good OLTP and OLAP designs are completely different. Another common mistake is mixing the two.

[dmarsh26]

This is denormalisation, theres many ways you can deal with it, you are assuming that the perfromance of the joins are very important. This is not necessarilly the case, you should not prematurely optimise your system.

Sure an in memory DB with a flat record stucture, single table is gonna perform the fastest, but thats not really what a relational database is about ? By duplicating data you create maintenance and consistency issues, if only an application reads a database who cares if its an Id ? The DBA's can create views, utils and stored procs etc to get whats meaningful to them.

[Crito]

No, it's not denormalization or optimization. It's proper design.

In an OLAP database you might want to use surrogate keys due to slowly changing dimensions (aka dimensional creep). In such cases you'll need to maintain duplicate natural keys, only one being the active or current natural key. This is usually accomplished with start and end dates or some sort of flag.

[Crito]

To be denormalization you'd have to move a non-key attribute/column/field over. And it's much faster to search a table on an integer than an alphanumeric. I also don't know why you'd think using a natural key would break referential integrity. That's what cascading updates and deletes are for.

[dmarsh26]

Maybe we are talking at cross purposes, my point was putting actual data rather than just a simple surrogate key results in duplication, especially when the natural keys are composite. This is very similar to denormalisation whereby you add in extra data to tables or merge tables to remove the requirement for joins.

Some people just never learn... even when their convoluted designs result in a dozen otherwise completely unnecessary joins. Looks at the fields you're retrieving from related tables. Chances are they're the natural keys, because the data is meaningless without them. You can't tell someone they live in stateid 14 and cityid 22, especially if they maintain their own cities and states tables with their own autonumbers.

Geographic regions are known to change over time, as are postal regions, hell even cities and countries in extreme cases. They are also text fields, so what makes them a good key ? Theres not even a large number really in any one country, in fact using surrogate keys would probaby perform better in this case, a typical application would cache the reference data on startup resulting in no unnecessary joins.

Who cares if other companies/departments maintain different keys in their DB, this is not your concern. If you want to join the two systems you need an integration layer. What happens if they are french or chinese ? They will have entirely different representations for reference data.

Conversion of strings values into meaningful data is a presentation or integration layer concern, what happens with i18n ? The UI/Web service/Import tool etc is responsible for the mapping. Enum/Reference data/Static lookup tables can be kept in the database to aid in usability of the database but is not required in all cases.

No, it's not denormalization or optimization. It's proper design.

Why is it 'good' or 'proper' design ? Duplication breaks the 'DRY' principle. String indexes are also likely to perform worse both in terms of storage and time. Readability can be added to the DB by using views that use the reference data tables. The interfaces and conversions should be dealt with on the boundaries of your system, who can tell what format they might expect the data ? Thats what UNICODE/ASCII/EBCIDIC/UTF8/XML/MIME... and the multitude of other data formats are for.