I’m catching up on my RSS feeds and I came across the latest SQL Down Under podcast with Greg Low and his guest for this show Graeme Simpsion. In the past I’ve been fortunate enough to attend a small two day workshop with Graeme Simpsion on consulting skills – I still apply lessons learned today!

Greg and Graeme touched on a number of important subjects ranging from the distinct roles of the data modeller and the database technician (as Graeme called it) to the choice of keys in databases and where rules belong.

The Data Modeller and The Database Technician

One of the things that Graeme does really well is simplify a potentially complex issue into its raw elements. He believes that there are actually two seperate roles involved with getting an information system up and running (ignoring the application development space for a moment). One is the Data Modeller and the other is the Database Technician. He also said that even those the roles are quite distinct, it is possible that they reside in the same person.

The modeller is someone who has a lot more exposure to the business whereas the engineer is someone who has a lot more exposure to the underlying database management system. He said - and I am paraphrasing here, a data modeller is someone who would find it relatively easy to migrate between database platforms, but difficult to change companies, whereas the database technician is someone who would find it relatively easy to change companies, but not migrate between database platforms. Very insightful!

Data Keys

One of the discussion points was common mistakes that people make in data modelling and naturally the subject of key selection came up. Most people fall into one of two camps – natural key people, or artificial key people. Graeme seems to cut through all of that and focus on key stability.

He contends that you want a key to be relatively stable because the costs of changing a key on a row are quite high from a code complexity point of view. We shouldn’t take that as a sign that he thinks we should all go out and use GUIDs though – or at least thats not the impression I got. He steered clear of naming a data-type to use – how diplomatic!

One thing he did point out however is that keys are distinct from identifiers, so while your key is something that helps with referential integrity, an identifier is something that helps people identify the data from outside the system, and they aren’t necessarily the same column.

Business Rules

There was a good discussion about business rules and where they exist. Greg cited an example of a speaker he had listened to who likes to put lots of constraints on individual columns in the database. The question is what impact does this have on the flexibility of the system (Graeme defines flexibility in data modelling terms in the podcast).

My take on it is that there are different ways to enforce business rules, structurally and via declartive constraints. Structual enforcement of rules is probably the most limiting because if you want to change a business rule you need to change the composition of entities in the conceptual schema which has huge flow on effects.

Declaritive rules like constraints provide a layer of defence at the database layer and can ultimately be dropped. The problem is that you can’t necessarily implement any given rule both ways or at all. A certain amount of analysis is required to determine whether the rule should be enforced in the database in the first place!

All in all it was a facinating podcast!