So recently, the topic of the moment in my brain has been generic table design vs traditional.
Back when I was a developer (sorry to come out of the closet like that….haha), I worked with a team of very skilled developers on a major project. The table designs used a mix of generic and traditional data model methods.
I can hear some of you thinking, ‘Uh?’.
Let me elaborate:
Traditional Data Models
This data model uses the standard primary unique numbered key, with data normalized to roughly the 3NF (3rd Normal Form). This means the database is able to use the standard tools of attack when parsing a query (index range scans, clustered index scans, etc). This makes for a faster, efficient data model (if designed correctly). It basically uses foreign key ids to maintain integrity. This is the safest model for data integrity and speed.
Generic Data Models (aka Nested Table Method)
So a “new” method of data modeling proving to be popular at the moment, and let me just say I use the word “new” very loosely, is the storing of primary keys as non-defined columns. Non-defined in that they are neither stored as PK or FK columns but as varchar2. The other data within the table is also generic columns. Now this causes an issue in terms of performance. No longer do the traditional PK system generated indexes get to come into force – the optimizer relies on the competency of the developer and DBAs involved.
Moving on…. so often with bespoke systems which have been developed over a series of years (usually 5 plus) development styles of people involved in the system get mixed up – new breed developers come on board to projects, keen to prove something. The old school developers bounce up too, often cynical (not all but some) of any new practice. I’ve met developers who insisted on:
- NOT changing the data model, requiring any addition (even if changing current primary data structures) to have a whole new set of tables and relationships to be created. Messy and performs like a bag of kittens!
- Implementing scripts found on other people’s blogs with no prior testing. Errr…which leads to arguments with DBAs…normally me
- Keen to redevelop an entire data model in their next modularly related project. (Yep. It’s true.)
- Insist on doing things ‘Their way’, which is hard basket – because often they don’t want to investigate any other option presented, resulting in heads being banged into brick walls. Painful.
Seeing as I was a developer for some time…. it pains me to see this pattern of behavior through the professional developer market. It pains me more when I am forced along the path of pointing out some of the flaws in the solutions presented. What’s even more scary is when my clients are having to pay out for – dare I say it – school boy quality work.
Hold on a second – let me stop the rant before I look like a sour grape. I am not trying to say here that I am perfect or that I know all – my point is this: Developers embrace what colleagues and business associates are saying to you. Test the options – often what you believe to do the right path, can be the wrong one. Testing options on solutions is the best way to prove you are right…. no need for textual arguments. It’s petty and when you’re wrong, can do some harm to either the relationship to your client or to your own credibility as an expert in the field.
So… let’s get back on point. It’s ok to use generic tables. They are useful in so many different contexts. One major negative: on general they are s l o w. They can’t integrate into an existing data model easily, particularly if a system has been in full swing for 15 years, hard coded and developed on a very traditional data model.
Stay away from primary keys being stored as references in child tables. It’s clunky, it’s slow and it won’t work.
So when looking at how to implement a data model, either for a project or for an existing system, think about the purpose of your code. Think about whether it needs to be fast. Think about what it is doing. Does it need to work that way? Can you do with out child to parent built queries? Is there a ‘trick’ way of presenting the information to the user? Sometimes code doesn’t need to appear sophisticated, it just needs to work.
Remember that.
Signing off for now.
TDZ.
