SQL for Smarties

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

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.

 

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

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.

 

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.

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.

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.