Bad Design: A Reminder to Document

Brian Walker writes about SQL Server database design disasters: What not to do. Unfortunately, these design failings are all too common. We all take different things from articles; my tangent is about a lack of documentation.

As a nerd, I would much rather do the fun part--creating, troubleshooting or anything else that requires thought. As a consultant, time and time again I have seen poor designs that are compounded by a lack of documentation. Not determining standards and and conventions, you facilitate others mutating a design with their own habits. Even if you have a poor system, the maintenance of it can be helped by documentation. Poor and consistent is a far cry better than poor and inconsistent. That makes it generally referred to as 'awful.'

It really does not take much time to document your approach. Simple documentation to outline the what and the why--even just a paragraph or two--might save countless hours down the line. Bad design is often perpetuated by lack of formalizing the business process. The least that a good database designer (or architect, if you prefer (I do--who doesn't want to be an architect?)) can do is document their approach; a naming convention document and a data dictionary. It is also important to do this before, during and after the design/implementation. Saying you will do it later typically means a rushed deadline is justification for not documenting (yeah, I have used that excuse too). Perhaps writing it out will key you into a couple weaknesses of your design... And wouldn't that make that horrible documentation process worthwhile?

Tags: design, documentation

More on Naming Standards

Ronald Bradford has a very nice article about naming conventions--and not just because we generally line up on our opinions.


Tags: design,
naming convention

Singular Table Name

Almost as bad as prefixing tables with a table-indicator is using plural names. Even Oracle tools default to this... It is just wrong.

A relation constitutes a header and a body. The header (or relational schema) is a set of attribute (column) and domain (value) pairs. The body is the set of tuples (rows). A tuple is a valued occurrence of the header.

Consequently, if you make your table names plural, make your column names plural for consistency.

Less mathematical justifications for using singular tables names include:

Consistency - Because sometimes you need 'es' to pluralize a word.
Consistency - Shouldn't 'ProductOrders' be 'ProductsOrders?'

Just like your mother taught you, just because everyone else is doing it, doesn't make it right.


Tags: design,
naming convention

Formatting

Formatting your code is just good practice. It matters less how you format your code than having a consistent appearance. I am using ‘formatting’ pretty loosely by including some general coding practices.

Recognizing that formatting SQL is largely preference, here is how I like it and why.


1. Keywords should be lowercase.

Many people go with uppercase; I find it distracting.

SELECT * FROM Widgets WHERE Quantity >= 20;

Looking at the above statement, we see ‘SELECT FROM WHERE!’ The heart of it is dwarfed by the keywords.


2. Use whitespace.

In simple examples, this seems tedious, but the point is that we demarcate the sections; we can easily see where each part of the statement is and what belongs to it.

select
    *
from
    Widget
where
    Quantity >= 20; 

Simple statements, we can overlook this. In a procedure, putting a singleton select or any obvious statement on one line with a comment immediately above it is fine as long as the statement is not overly complex.

-- Get the widget name
select Widget into xWidget from Widget where WidgetID = 168; 

I am old school, so I miss table joins in the where clause; the reason being formatting. The formatting looks awkward with joins in the from clause since there is no good way to get all of the pieces to line up.

Example 1a:

select
    a.x,
    a.y,
    b.c
from
    a
    inner join b
        on b.a = a.a
where
    a.c = 1; 

Example 1b:

select
    a.x,
    a.y,
    b.c
from
    a,
    b
where
    b.a = a.a

    and a.c = 1; 

Depending on what tools you are using, you may have some whitespace sensitivity issues (which really makes me angry). But the second statement reads easier to me. I immediately see that there are two tables, and one join.

The first statement is a bit cumbersome. I see the inner join and the on, so I assume that they are joined properly. It is really just the formatting that I do not care for. You could go with:

from
    a inner join
    b
        on b.a = a.a

The annoyance being that as soon as you alias your tables, it decreases the readability. The nice part of joining in the from clause is that it reduces the likelihood of Cartesian products. I am not condoning performing joins in the where clause, just mentioning that I prefer everything simpler (lines up better that way).


3. Alias tables

Meaningful tablenames segue into easy aliases. I can see using ‘a,’ ‘b’ and ‘c’ in examples, but in the real world they make it more complicated than not aliasing. You do not arbitrarily name tables—treat aliases the same.

select
    m.Manufacturer,
    p.Product + ‘ ‘ + p.Size Product,
    q.Quantity
from
    Manufacturer m,
    Product p,
    Quantity q
where
    p.ManufacturerID = m.ManufacturerID
    and q.ProductID = q.ProductID;

4. Use comments 

By and large, I think that code is self-documenting. I prefer block comments (‘/* … */’) for ideas, or logical separations. I use inline comments (‘-- …’) to describe a statement. I comments statements only if they do not read easily. If you are nesting a few functions, or a non-intuitive clause, comment it. I consider conditional operations a logical idea and typically comment them.

-- Number of whole weeks times five days each
oDay := ((floor((xEnd - xStart) / 7)) * 5);

-- Add difference in days outside of whole weeks
oDay := oDay + mod((xEnd - xStart), 7);

5. Use Blocks

Parentheses and begin/end allow you to show your intentions. Much like whitespace, it delineates your ideas, and is often syntactically required.

At a minimum, anytime I use an OR clause, I put parentheses around my idea to show that the condition was deliberate.


I believe that those are my top preferences. Take the ACID properties beyond transactions. What are your thoughts?

Good Database Design Part 7: Stored Procedures

Most DBAs are aware of the many benefits of using stored procedures for data access; in short, performance and security. By using stored procedures we can offload some to all of the business rule enforcement that is not handled by constraints.

Database servers tend to bottleneck more on I/O than memory or CPU. The hardware backing a database server tends to be stronger than an application or web server. Using some of those free resources can ease the burden on your application servers while reducing the amount of network traffic and database transactions. Why would you want to worry about in-line SQL and ad hoc queries? Maintenance, permissions and tuning are often complicated by using such primitive methodologies. Stored procedures are beautiful things.

By using one procedure for inserts and updates, you can minimize the types of calls to a database, simplifying the developer’s job. If you are using surrogate keys, it is very simple to implement:

create procedure Client_Add(iClient_UID, iClientName, iDateContract)
as
begin
    if(iClient_UID = 0 or iClient_UID is null) then
        insert into
            Client(
                ClientName,
                DateContract
            )
            values(
                iName,
                iDateContract
            );
    else
        update
            Client
        set
            ClientName    =    iClientName,
            DateContract    =    iDateContract
        where
            Client_UID    =    iClient_UID;
    end if;
end;

If you are not using surrogate keys, you need to check if the record exists based on the primary key (not a bad thing to do even if you are using surrogate keys).

Tables with foreign keys can be translated and/or validated. In many cases, we can get away from costly triggers by using stored procedures to handle translations. Validate your foreign keys

Similarly for the retrieves, you can use the surrogate key to determine whether to grab a specific row or some to all rows.

While procedures facilitate dynamic SQL beware that they introduce similar risks as inline SQL. Always validate your inputs.

Good Database Design Part 6: Surrogate Keys

I routinely use surrogate keys. Migrating one integer value instead of a wider compound key has numerous benefits. It provides nice consistency across the physical model, by and large saves more space than it costs and reduces I/O when compared to migrating compound keys; especially in a well-normalized model. Additionally, they simplify understanding of a model and query joins. Yet another benefit of surrogate keys is that they trivialize identification of new rows from existing rows (covered in Part 7: Stored Procedures).

Most DBMS's provide a simplified way of accomplishing this (as well they should since it made its way into the SQL:2003 standard); MySQL uses an AUTO_INCREMENT property, Oracle has sequences (less simple than others because sequences are not associated with a field, so often automated using triggers), PostgreSQL uses a SERIAL datatype, where DB2, SQL Server and Sybase use an IDENTITY property.

Oracle and Microsoft often show examples of this ID column methodology. The important thing to remember is that this does not excuse you from defining your logical keys. They may not need to be physically enforced, but they should at least be documented. It is important to realize that your surrogate keys are only at the physically level. Logically, they do not exist. Logically, you are still responsible for primary and candidate keys.

The type is a part of SQL:2003 which reflects the community’s general acceptance of surrogate keys. Still dodgy ground for some DBAs, most recognize the value of this approach.

I already touched naming conventions, but consider that renaming columns is by and large unnecessary (there are exceptions) and ‘id’ is often a reserved word. Using the name format of ‘TableName_UID’ (or ‘TableNameID’) shows where surrogate keys are from and easily shows which tables are related.

Good Database Design Part 5: Physical Design

Convert the logical model into an actual database. This is where you work within the limitations of your DBMS and hardware, keeping the physical database design as close to the ideal of the logical model as possible.

If you are fortunate enough to have good modeling software, most of the physical design may be taken care of for you. And you are probably designing against an ERD, which, I find much easier.

In Part Three I said, ‘If you cannot arrange your model so that lines do not cross over each other, you may have some logical flaws.’ I think that this holds true through the physical model. The only exception that I have seen is using a universal reference table; one reference table (that is generally two tables--one for type and one for values) instead of specific reference tables. Personally, I do not like this approach. More accurately, I like the approach as it is a neat concept, but poor implementation. There are often a couple of items that require more than one attribute, so what do you do about the exceptions? Additionally, you are renaming your columns to reflect the reference value which is lame (and breaks natural joins (you use natural joins all the time, right?) or not renaming your columns in which case you cannot have more than one reference domain within a relation and your names do not reflect the actual attribute which is even lamer or a combination of the two which is the lamest by far.

How do the entities translate into tables? Oft times they will be identical to the logical model. There are certain constructs that do require changes. A lot of this depends on how you modeled the logical design. If you created a very thorough logical model, it may contain business rules that will need to be enforced via code mechanisms, and not immediately reflected in relations. It may be nearly identical to your logical model.

Ensure that any structural changes you make at the physical level are reviewed. There is the possibility that translating something changed an existing relationship or even broke a primary key.

Creating the tables is really just the beginning of the physical design. It is critical as it is the foundation, but there is a lot more to do.

Good Database Design Part 4: Normalization

Ideally, normalize it so hard that an understanding of abnormal is abolished. Unfortunately, we do not live in an ideal world. The database administrator desires normalization while the developer craves denormalization. Good design is simple to represent denormalized.

C.J. Date said that database design is common sense formalized. Expounding upon that, you do not need to know relational theory to be capable of good design. Hopefully, you enjoy what you are doing enough to take an interest, but it is by no means mandatory. Critical thinking will often get you farther than theory alone.

With that said, normalization is great theory. The naysayer will whine about performance costs. You may be reading more tables, but you are doing it more efficiently which just as often results in… wait for it… less I/O and/or quicker retrieval times. The naysayer is generally considering only one side of performance: reading. A well normalized model yields more joins than a flatter model, and joins are expensive. However, more and smaller tables may improve write operations, overall. Additionally, a denormalized model often requires more supporting indices. This further degrades write performance, may increase fragmentation, requires more disk space and--getting overly nitpicky--complicates the optimizer’s job by forcing consideration of additional plans. I am not saying that denormalization does not have a place; just that it is typically in an analytical/reporting environment. Nor am I saying that normalized design does result in poor read performance. Poor implementation results in poor performance; don’t blame normalization (you can blame DBMSs for some of this (SQL should better provide for relational algebra operations)). If someone is using performance as an excuse not to normalize their OLTP model, there is a strong chance that they are an idiot.

There is no need to take everything to the fifth normal form; a good DBA knows when to be a bit forgiving—but you best have some sound reasoning backing you up. For most applications, the third normal form is sufficient. Start with your ideal model and adjust it around your implementation. Your concessions should be small to none for OLTP models. You can present it to your application developers however they want it; don’t let them break your rules unless you cannot accommodate their needs.

A well normalized model results in a database containing tables with an average of six to eight fields. Outside of averages, trust your intuition. If something looks good and feels correct (you kept it simple, right?), it probably is—move on.

There are standard structures that are much easier not to normalize. Names and addresses, for instance. Most of the time, we turn a blind eye and throw some columns into a table because it is much easier than normalizing. This does not mean that names and addresses should be in one table; obviously, if the entity associated with the address can have multiple addresses, that needs to be factored into your design.

Consider the selectivity of your data. If it is low, maybe normalizing saves some space. If not, maybe we can let this slide (like addresses and suite numbers). Also consider your methodology. If you are using an integer as your surrogate key, what will normalizing area codes do? If you store the area code as an integer, it actually adds the domain times two and creates an additional join. If the area code is three characters, that is smaller than the key. Don’t get carried away with normalizing your design, but remain vigilant.

I once implemented a project where normalizing the names and migrating surrogate keys saved a lot of space and did not sacrifice performance (quite the opposite). Look at the Social Security Administrations Death Master File; millions of names with only a couple hundred thousand unique first names and surnames. This is an anomaly (not the names, normalizing the names). The important thing is being aware of all the factors and accounting for them in your design.

Good Database Design Part 3: Logical Design

Armed with requirements and a naming convention, we are ready to commence designing.

The logical model is a diagram of how the requirements will be structured; not how they will be implemented.

One of Einstein’s great quotes, ‘Make everything as simple as possible, but not simpler.’ Excellent words to live by, and definitely to design by. Even the most complex problems are a series of simple steps. Break it down.

Many of the entities of the logical model will translate easily into physical database tables. This should not be a factor of the process; it is just a convenient byproduct. It is important to keep the logical model free from physical restrictions—you do not care how your DBMS implements things. You do care about modeling the data structure as defined by the business requirements.

This may sound rather odd, but--so far--I swear by it. If you cannot arrange your model so that lines do not cross over each other, you may have some logical flaws. I recognize that your model is not actually flat. I also recognize that on larger models this may be even more difficult. However, when I see lines crossing on an ERD, it often indicates unnecessary joins. These may be in place at the physical level to simplify joins (not that that is any excuse), but they really should not show up on a logical diagram.

Embarcadero’s ER/Studio is one of the finest database products I have ever used. It is the single greatest modeling tool that I have ever used. And, unfortunately, it is prohibitively expensive for many outfits. Someone needs to explain the economic cost curve to them (yeah, had to look that one up—Econ 101 was a long time ago). For a nice freeware diagramming tool, DBDesigner (unfortunately, it is only for MySQL, but is capable of a decent looking ERD).

Modeling can be one of the most engaging areas of database design. You are creating something with a purpose without worrying about the physical and implementation limitations. Granted, you may not be able to do anything clever yet because you are bound by the business requirements. Still, modeling is more enjoyable than documenting.

Good Database Design Part 2: Naming

Would you prefix your database name to indicate that it is a database? If so, you need to stop reading this and never call yourself a database administrator (preferably don’t even associate yourself with anything in the database realm and should probably never consider yourself a critical thinker, but I don’t want to get hurtful). I feel just as adamantly about prefixing tables to indicate that it is a table—‘tbl,’ ‘t,’ ‘t_’—it is horribly wrong. These are the base objects of a database. Hungarian notation is good. Note that you don’t see variables types prefixed; have you ever seen a variable named ‘TypIntFoo?’ Tables—no prefix. Ironically, the people committing this offense typically do not prefix their column names with data types. I would love some insight into that reasoning.

This is just my opinion, but I back it up with C.J. Date holding the same. As well, I have never seen a professionally designed database prefixing tables. I have seen commercial products that do not even meet first normal form, have a minimal semblance of design, that require escalated permissions to run; and they still are not prefixing tables with a ‘table’ indicator. Prefix (would ‘object typing’ be a better term?) your views if you must, stored procedures, what have you. Leave the tables alone.

Outside of that gripe, I do not care how you do it; just remain consistent throughout your model. Just found a second annoyance. I have seen databases where half is decently designed, and half uses a completely different naming convention (and often not so well designed). Stick to the initial design and naming convention. Cohesiveness facilitates maintenance. That includes using comment blocks of the initial development push (or changing them all to what you want). I recognize the world of IT is not the most team oriented (I am a misanthrope myself) but with the glory goes the blame.

What do I prefer? All objects named in sentence-case bearing full and descriptive names (which does not work so well with Oracle, unless double-quotes everywhere are appealing to you). Most DBMS’s today have generous limits on object names; take advantage of it. Try to keep the abbreviations to a minimum as they require knowledge of the business to determine meaning.

The only place I do not mind underscores are for contrived keys (TableName_UID (although TableNameID works too)) and for those rare situations when you have to rename a migrated column (for instance, FooID migrated twice to one table, one of them becomes Purpose_FooID). By using underscores like this, it serves as a visual cue that we are dealing with a physical element.

I prefer each relation to have a one or two word name (this leads to convenient understandable aliases). I also like grouping my ideas together by migrating part of the name:

ServerNetwork --- Server --- ServerBackup --- BackupVolume

It presents a nice way of physically reflecting the logical flow (we see two tables related to server, which is a base idea and two related to backups which relates to servers).

Table names should be singular. If you do not do that, it gets awkward quick. ‘Servers may be ok, ‘ServersNetworks’ is rather lame and ‘ServerNetworks’ is inconsistent in at least one sense. And what about a table named ‘Equity?’ I sometimes use plural forms for view names. When they are a bit denormalized, you are illustrating a larger idea and the plural form may capture that. Again, C.J. Date agrees. Relations are types and types are singular (regardless of what Oracle Designer defaults to).

If names have repeating elements, I often lead with the repeating bits, for better or worse (NameLast, NameFirst and DateStart, DateEnd).

For each model, try to incorporate the users’ terminology into your design, where appropriate. Do not force a name out of habit. This makes it easier on the report monkeys and shows your understanding of the business rules.

The important point is using a consistent naming convention throughout a model. Like most things, preference weighs in heavily. Do not be afraid of modifying your naming convention; it should evolve with your experience.

Good Database Design Part 1: Requirements

Good database design begins before the model. Documentation and user interviews may not be enjoyable to you, but if you want your database to fulfill the business needs it is supposed to address, they are critical processes. It is important that your client is thinking about what data the application needs to be useful. Knowing the scope of the application and what business purpose(s) it fulfills clarifies your purpose and process.

Once the functional requirements are documented, the technical specifications should start taking shape. It is imperative that you ask questions clarifying how the data relates to other elements and discover additional business rules and data items. Users are generally more familiar with how the data relates to itself than they are aware. Asking a few simple questions makes your job a lot easier. Listen to your client; it will make your job easier and the project more successful. You do not want to intimidate the user(s) so do not speak about attributes, relations and domains. Instead, make them feel smart by speaking their language; ask if a member will ever have multiple addresses. Getting a handle on what data needs to be stored and how it relates to other data facilitates the database designing itself (seriously, they do that).

A well designed database accommodates all reporting so use the reporting requirements to ensure that you have all relevant elements; you should not need to adjust your design for reports. If you do, it may point to a design flaw or two.

To recap, requirements are important. They provide you an understanding of the client’s needs and show your understanding of what data needs to be stored and how the data relates to itself. Participate in the requirement gathering as the rest of your team may not ask the questions that you need answered. Requirements are the start of your database.