Quickly Check for Database Design Flaws

Don't you just hate it? You've finished reading, again, that blog entry about database design and you're feeling that you can design something reasonable, and then you see this table:

EmployeeID| SalesPerson| SalesOffice| OfficeNumber| Customer1| Customer2| Customer3
1003 Mary Smith Chicago 312-555-1212 Ford GM
1004 John Hunt New York 212-555-1212 Dell HP Apple
1005 Martin Hap Chicago 312-555-1212 Boeing

You can easily see that Customer1, Customer2, and Customer3 are wrong, but what about the rest? Try as you might, you can't quite put all of the rules together that easily to figure out what's wrong with the above table.

There's a shortcut, though, and it makes it very easy to start understanding database design.

If you've read my blog post, How to fake a database design, or seen my related talk, you know that I focus heavily on nouns.

Take a look at the above table and pull out the nouns. You see that we have employees, offices, and customers. If you assume that each noun probably requires a separate table, you have a great start at normalization without necessarily knowing what the final database form will look like.

In the above, we know we might have an employees table, an offices table, and a customers table, all just by separating the nouns. At this point, you ask "what if an employee is also a customer?" That's where expertise comes in because it's often tricky knowing how to separate out issues like this.

For a counter-example of separating nouns, consider a recipes table:


directions is a noun, so why isn't that in a separate table? Because the name column is merely a shorthand for directions. You could argue that if two recipes had the same directions, they're the same recipe. Thus, the noun directions is wholly dependent on the key.

Here's a different, but common example:


You see that so often that it almost looks natural, but what if you have more than one customer at an address? What if a customer has more than one address? Should your database have addresses without customers? Because customer and address are both nouns and the address is clearly not wholly dependent on the customer, it belongs in a separate table.

So there you have it: if you put aside the rules of normalization for a moment and just focus on the nouns, a lot of the low-hanging fruit of database design jumps out at you.

For added fun, read A Simple Guide to Five Normal Forms in Relational Database Theory (pdf).


I agree that natural language offers important clues on how to design the data model. But it seems to me your advice is too simple, and not literally correct.

You seem to argue that whenever there is noun we should consider a separate table. But there are nouns everywhere, way more than you mention.

For instance, in the recipe example, "customer_id" and "name" are also nouns. Yet we know they should not have a separate table.

To extract meaningful clues from the nouns we have to look further at what kind of noun it is.

For example, if it is a compound noun, then that often indicates a propery of some entity ("customer_id" is the "id" of the "customer").

The noun "directions" also provides an important clue: because it is a plural, there can be multiple things called "direction" associated with a recipe. So contrary to how you treat it, I do not consider it a shorthand for the name of the recipe: I would give the recipe a child table to hold its list of directions. This is apt since a direction has properties of its own: apart from the sequence within the other directions and the text describing what one has to do, it can also have an indication of duration. And if we'd want to normalize even further a direction could itself have a list of (references to) the utensils that are required to complete the task conveyed by the direction.

If the noun is based on a verb then that can indicate we're dealing with a many to many relationship, and constructing a sentence with such a verb can provide clues about which kinds of things are related to one another. "Order" is a simple example: If we have an order, then someone ("customer") is ordering something ("products") from someone else ("store"). While the verb does not contain any information as to exactly which things are related and how exactly they are related, the mere occurrence of a verbal known can serve as an indication that further investigation is required to determine whether it constitutes a relationship between several tables.

Ovid, my comment about directions was just to illustrate that a plural noun (as opposed to a singular) often indicates a one to many relationship. And rather than treating it as an exception right away, I showed how that would play out in this case.

You state that I am overthinking the problem and that directions is a "perfect example of unstructured data". I would say that it is a perfect example of how the requirements of your database determine whether it is to be treated as structured or unstructured.

If your main requirement is fast, read-only query access to recipes, then we might consider not normalizing at all and storing recipes as documents, and only extracting the most basic items ("name") and store those in a structured matter. More fancy requirements, like alllowing "variation" recipes (say, cake and a gluten free variation of the same cake), allow queries on the ingredient level ("filter for gluten-free recipes"), the ability to view recipes using localized measures for weight, volume, temperature, then imposing more structure will be the way to go. Even a modest requirement to have recipes report a ingredient list that is consistent with the directions already requires structuring the directions.

For this reason it seems impossible to say that some kind of data is a perfect example of unstructured data, for the simple reason that "unstructured" is not a property of the data but of our requirements.

You mention that this is just a quick check for beginners. But what do you tell such a beginner when they point out to you that according to the dictionary, "name" is also a noun? It seems to me all of the fields you mentioned are nouns, so how to distinguish which one become tables and which ones become columns? Distinguishing between plural/singular and compound nouns and verbal nouns is already a step in the right direction.

About Ovid

user-pic Freelance Perl/Testing/Agile consultant and trainer. See http://www.allaroundtheworld.fr/ for our services. If you have a problem with Perl, we will solve it for you. And don't forget to buy my book! http://www.amazon.com/Beginning-Perl-Curtis-Poe/dp/1118013840/