SQL Server Infernals – Circle 4: Anarchic Designers


Infernals

Constraints are sometimes annoying in real life, but no society can exist without rules and regulations. The same concept is found in Database Design: no good data can exist without constraints.

What they say in Heaven

Constraints define what is acceptable in the database and what does not comply with business rules. In Heaven, where the perfect database runs smoothly, no constraint is overlooked and all the data obeys to the rules of angels:

  • Every column accepts only the data it was meant for, using the appropriate data type
  • Every column that requires a value has a NOT NULL constraint
  • Every column that references a key in a different table has a FOREIGN KEY constraint
  • Every column that must comply with a business rule has a CHECK constraint
  • Every column that must be populated with a predefined value has a DEFAULT constraint
  • Every table has a PRIMARY KEY constraint
  • Every group of columns that does not accept duplicate values has a UNIQUE constraint

Chaos belongs to hell

OK: Heaven is Heaven, but what about hell? Let’s see what will get you instant damnation:

Anarchy

  1. Using the wrong data type: we already found out that the SQL Server hell is full of Shaky Typers. The data type is the first constraint on your data: choose it carefully.
  1. No PRIMARY KEY constraints: In the relational model, tables have primary keys. Without a primary key, a table is not even a table (exception made for staging tables and other temporary objects). Do you want duplicate data and unusable data? Go on and drop your primary key.
  1. NULL and NOT NULL used interchangeably: NOT NULL is a constraint on your data: failing to mark required columns with NOT NULL will inevitably mean that you’ll end up having missing information in your rows. At the same time, marking all columns as NOT NULL will bring garbage data in the database, because users will start using dummy data to circumvent the stupid constraint. We already met these sinners in the First Circle of the SQL Server hell.
  1. No Foreign Key constraints: Foreign Keys can be annoying, because they force you to modify the database in the correct order, but following the rules pays off. Without proper constraints, what would happen if you tried to delete from a lookup table a key referenced in other tables? Unfortunately, it would work, silently destroying the correctness of your data.
    What would happen if you tried to sneak in a row that references a non-existing key? Again, it would bring in invalid data.
  1. No CHECK constraints: Many columns have explicit or implicit constraints: failing to add them to the database schema means that values forbidden by the business rules will start to flow into the database. Some constraints are implicit, but equally important as the explicit ones. For instance:
    • an order should never be placed in a future date
    • a stock quantity should never be negative
    • a ZIP code should only contain numeric characters
    • a Social Security Number should be exactly 9 digits long
  1. Relying on the application to validate data: If I had €0.01 for every time I found invalid data in a database and the developers said “the application will guarantee consistency”, I would be blogging from my castle in Mauritius. Maybe the application can guarantee consistency for the data that it manipulates (and it won’t, trust me), but it can do nothing for other applications using the same database. Often the database is a hub for many applications, each with its own degree of complexity and each with its level of quality. Pretending that all these applications will independently guarantee that no invalid data is brought in is totally unrealistic.

 The last circle of SQL Server hell dedicated to Database Design sins is the circle of Inconsistent Baptists, those who fail to comply to sensible naming conventions. Stay tuned!

Advertisements

Posted on July 7, 2015, in SQL Server Infernals and tagged , , , , , , , . Bookmark the permalink. 4 Comments.

  1. Hmm… these are all good, until you get some “enterprise database software”, especially if it’s very OLTP-oriented, and look under the covers (e.g., Oracle Financials, etc). Most or all of these idioms are NOT used, and it really is leap-of-faith that the app layer(s) take care of it.

    For OLTP space, when you get a large table (rows x columns > 1,000,000, and # users > 1), it can turn into a serious performance hit doing all those index checks, referential integrity checks, etc. for a simple data “update”.

    Just sayin’.

    Like most things SQL Server, they’re good to start out with, and are perfect in an ideal world, but… don’t be too dedicated to the dogma, either.

    • Heh, Corey…I would agree with you if people designed databases without constraints for performance reasons, well aware that they’re amending the best practices for a particular, well circumstantiated issue.
      Unfortunately it is not so in the vast majority of cases: people work without constraints because they don’t know better.

      However, let me say that a database needs consistency MUCH more than it needs performance. If the application needs more performance, find a workaround, but not sacrificing correctness and consistency.

  1. Pingback: BPOTW 2015-07-10 | SQL Notes From The Underground

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: