Times change. In the good old days “good” relational database design was rather straight forward, there were simply tables with columns and rows plus rather simple indexing options. Thus database designers merely had to generally employ just two fundamental “best practices”: sensible table normalization and minimal, effective indexing. Table normalization (when not overdone) was an important but quite often misunderstood paradigm. Database designers generally presumed that normalization accomplished five primary goals:

 

  1. Eliminate duplication of data
  2. Reduce modification anomalies
  3. Provide a flexible & extensible design
  4. Avoid bias towards any query patterns
  5. To be end-user friendly & informative

 

The problem was most people also assumed that the first goal was key in order to reduce the overall size of the database since (in the past) disk was expensive. In fact the first goal was not a goal at all, it was merely a side-effect required to accomplish the second goal. Hence the general misunderstanding. Nonetheless one could review a database design and judge/debate its normalization level rather easily.

 

Indexing on the other hand was fairly well understood and generally suitably implemented since in most cases there were relatively few indexing factors:

 

  • Unique or not
  • Column data types
  • Column order (i.e. ascending vs. descending)
  • Data value selectivity
  • Number of columns
  • Overall index size

 

Then times began to change. First index design became far more complicated due to numerous and in some cases novel new database features related to indexing. Indexing options now additionally include the following factors:

 

  • Index type
    • b-tree indexes
    • reverse order indexes
    • bitmap indexes
    • bitmap join indexes
    • function based indexes
    • cluster indexes
    • domain indexes
  • global vs. local (i.e. partitioning)
  • index organized tables
  • indexing on virtual columns
  • indexing on nested tables
  • indexing on substitutable columns
  • indexing on XML type columns

 

So what was once easy is now quite challenging. And since indexes often play a key role in SQL explain plans, indexes are now one of the hardest things to both effectively and efficiently design.

 

But what really obscures “good” relational database design are all the new industry directions related to data. Over the past decade or so we now have many more influencing factors:

 

  • System of record
  • Operational Data Stores (i.e. ODS)
  • Data warehouses (i.e. star schema design)
  • Unstructured data
  • Column oriented (vs. relational)
  • Big data
  • Analytics
  • NoSQL
  • Cloud
  • etc.

 

The point is that now table design is far more subjective based on the need at hand and requirements. I would not presume to debate anyone’s database design with so many factors, because the right answer would now entirely depend on what paradigm is being used and for what purpose or goal. There now is no wrong answer.

 

Thus we’ve had a complete reversal of database design challenges – what once was easy (i.e. indexing) is now extremely hard, and what once was fairly less subjective (i.e. table design) now requires far more contextual insight. So while in the past I could fairly easily identify a bad database design, today I would not presume to make such a call. So what is “good” database design? It literally all depends.