All Columns Must Be Atomic

The goal of this requirement is that each column should represent only one value, not multiple values. This means there should be nothing like an array, no delimited lists, and no other types of multivalued columns that you could dream up represented by a single column. For example, consider a group of data like 1, 2, 3, 6, 7. This likely represents five separate values. This group of data might not actually be multiple values as far as the design is concerned, but for darn sure it needs to be looked at.

One good way to think of atomicity is to consider whether you would ever need to deal with part of a column without the other parts of the data in that same column. In the list mentioned earlier—1, 2, 3, 6, 7—if the list is always treated as a single value in SQL, it might be acceptable to store the value in a single column. However, if you might need to deal with the value 3 individually, the value is definitely not in First Normal Form. It is also important to note that even if there is not a plan to use the list elements individually, you should consider whether it is still better to store each value individually to allow for future possible usage.

One variation on atomicity is for complex datatypes. Complex datatypes can contain more than one value, as long as

• There is always the same number of values.
• The values are rarely, if ever, dealt with individually.
• The values make up some atomic thing/attribute that could not be fully expressed with a single value.

For example, consider geographic location. Two values are generally used to locate something on Earth, these being the longitude and the latitude. Most of the time, either of these, considered individually, has some (if incomplete) meaning, but taken together, they pinpoint an exact position on Earth. Implementing as a complex type can give us some ease of implementing data-protection schemes and can make using the types in formulas easier.

When it comes to test of atomicity, the test of reasonability is left up to the designer (and other designers who inherit the work later, of course), but generally speaking, the goal is that any data you ever need to deal with as a single value is modeled as its own column, so it's stored in a column of its own (for example, as a search argument or a join criterion). As an example of taking atomicity to the extreme, consider a text document with ten paragraphs. A table to store the document might easily be implemented that would require ten different rows (one for each paragraph), but there's little reason to design like that, because you'll be unlikely to deal with a paragraph as a single value in the SQL database language. Of course, if your SQL is often counting the paragraphs in documents, that approach might just be the solution you are looking for (never let anyone judge your database without knowledge of the requirements!).

As further examples, consider some of the common locations where violations of this rule of First Normal Form often can be found:

• E-mail addresses
• Names
• Telephone numbers

Each of these gives us a slightly different kind of issue with atomicity that needs to be considered when designing columns.

All Rows Must Contain the Same Number of Values

The First Normal Form says that every row in a table must have the same number of columns. There are two interpretations of this:

• Tables must have a fixed number of columns.
• Tables should be designed such that every row has a fixed number of values associated with it.

The first interpretation is simple and goes back to the nature of relational databases. You cannot have a table with a varying format with one row such as {Name, Address, Haircolor}, and another with a different set of columns such as {Name, Address, PhoneNumber, EyeColor}. This kind of implementation was common with record-based implementations but isn't possible with a relational database table. (Note that, internally, the storage of data in the storage engine does, in fact, look a lot like this because very little space is wasted in order to make better use of I/O channels and disk space. The goal of SQL is to make it easy to work with data and leave the hard part to other people).

The second is a more open interpretation. As an example, if you're building a table that stores a person's name and if one row has one name, all rows must have only one name. If they might have two, all rows must have precisely two (not one sometimes and certainly never three). If they may have a different number, it's inconvenient to deal with using SQL commands, which is the main reason a database is being built in an RDBMS! You must take some care with the concept of unknown values (NULLs) as well. The values aren't required, but there should always be the same number (even if the value isn't immediately known).

You can find an example of a violation of this rule of First Normal Form in tables that have several columns with the same base name suffixed (or prefixed) with a number, such as Payment1, Payment2, for example:

Now, to add the next payment for Fred's, we might use some SQL code along these lines:

 UPDATE dbo.Customer

 SET Payment1 = CASE WHEN Payment1 IS NULL THEN 1000.00 ELSE Payment1 END

  Payment2 = CASE WHEN Payment1 IS NOT NULL AND Payment2 IS NULL THEN

  1000.00 ELSE Payment2 END,

  Payment3 = CASE WHEN Payment1 IS NOT NULL

  AND Payment2 IS NOT NULL

  AND Payment3 IS NULL

  THEN 1000.00 ELSE Paymen3 END

 WHERE CustomerId = 0000230003;

And of course, if there were already three payments, you would not have made any changes at all. Obviously, a setup like this is far more optimized for manual modification, but our goal should be to eliminate places where people do manual tasks and get them back to doing what they do best, playing Solitaire . . . er, doing the actual business. Of course, even if the database is just used like a big spreadsheet, the preceding is not a great design. In the rare cases where there's always precisely the same number of values, then there's technically no violation of the definition of a table, or the First Normal Form. In that case, you could state a business rule that "each customer has exactly two payments." In my example though, what do you make of the fact that Payment2 is null, but Payment3 isn't? Did the customer skip a payment?

Allowing multiple values of the same type in a single row still isn't generally a good design decision, because users change their minds frequently as to how many of whatever there are. For payments, if the person paid only half of their expected payment—or some craziness that people always seem to do—what would that mean?. To overcome these sorts of problems, you should create a child table to hold the values in the repeating payment columns. Following is an example. There are two tables. The first table holds customer names. The second table holds payments by customer.

The first thing to notice is that I was able to add an additional column of information about each payment with relative ease—the date each payment was made. You could also make additions to the customer payment table to indicate if payment was late, whether additional charges were assessed, whether the amount is correct, whether the principal was applied, and more. Even better, this new design also allows us to have virtually unlimited payment cardinality, whereas the previous solution had a finite number (three, to be exact) of possible configurations. The fun part is designing the structures to meet requirements that are strict enough to constrain data to good values but loose enough to allow the user to innovate within reason.

Now, with payments each having their own row, adding a payment would be as simple as adding a new row to the Payment table:

 INSERT dbo.Payment (CustomerId, Amount, Number, Date)

 VALUES ('000002324', $300.00, 2, '2012-01-01'),

You could calculate the payment number from previous payments, which is far easier to do using a set-based SQL statement, or the payment number could be based on something in the documentation accompanying the payment or even on when the payment is made. It really depends on your business rules. I would suggest however, if paymentNumber is simply a sequential value for display, I might not include it because it is easy to add a number to output using the ROW_NUMBER() windowing function, and maintaining such a number is usually more costly than calculating it.

Beyond allowing you to easily add data, designing row-wise clears up multiple annoyances, such as relating to the following tasks:

• Deleting a payment: Much like the update that had to determine what payment slot to fit the payment into, deleting anything other than the last payment requires shifting. For example, if you delete the payment in Payment1, then Payment2 needs to be shifted to Payment1, Payment3 to Payment2, and so on.
• Updating a payment: Say Payment1 equals 10, and Payment2 equals 10. Which one should you modify if you have to modify one of them because the amount was incorrect? Does it matter?

If your requirements really only allows three payments, it is easy enough to implement a constraint on cardinality. As I showed in Chapter 3 for data modeling, we control the number of allowable child rows using relationship cardinality. You can restrict the number of payments per customer using constraints or triggers (which will be described in more detail in Chapter 7) but whether or not you can implement something in the database is somewhat outside of this part of the database design process.

All Rows Must Be Different

One of the most important things you must take care of when building a database is to make sure to have keys on tables to be able to tell rows apart. Although having a completely meaningful key isn't reasonable 100 percent of the time, it usually is very possible. An example is a situation where you cannot tell the physical items apart, such as perhaps a small can of corn (or a large one, for that matter). Two cans cannot be told apart, so you might assign a value that has no meaning as part of the key, along with the things that differentiate the can from other similar objects, such as large cans of corn or small cans of spinach. You might also consider keeping just a count of the objects in a single row, depending on your needs (which will be dictated by your requirements).

Often, designers are keen to just add an artificial key value to their table, using a GUID or an integer, but as discussed in Chapter 1, adding an artificial key might technically make the table comply with the letter of the rule, but it certainly won't comply with the purpose. The purpose is that no two rows represent the same thing. An artificial key by definition has no meaning, so it won't fix the problem. You could have two rows that represent the same thing because every meaningful value has the same value, with the only difference between rows being a system-generated value. Note that I am not against using an artificial key, just that it should rarely be the only defined key. As mentioned in Chapter 1, another term for such a key is a surrogate key , so named because it is a surrogate (or a stand in) for the real key.

A common approach is to use a date and time value to differentiate between rows. If the date and time value is part of the row's logical identification, such as a calendar entry or a row that's recording/logging some event, this is not only acceptable but ideal. Conversely, simply tossing on a date and time value to force uniqueness is no better than just adding a random number or GUID on the row.

As an example of how generated values lead to confusion, consider the following subset of a table with school mascots:

Taken as it is presented, there is no obvious clue as to which of these rows represent the real Smokey, or if there needs to be more than one Smokey, or if the data entry person just goofed up. It could be that the school name ought to be included to produce a key, or perhaps names are supposed to be unique, or even that this table should represent the people who play the role of each mascot. It is the architect's job to make sure that the meaning is clear and that keys are enforced to prevent, or at least discourage, alternative interpretations.

Of course, the reality of life is that users will do what they must to get their job done. Take, for example, the following table of data that represents books:

The users go about life, entering data as needed. But when users realize that more than one author needs to be added per book, they will figure something out. What a user might figure out might look as follows:

The user has done what was needed to get by, and assuming the domain of BookISBN allows it, the approach works with no errors. However, DMV Book looks like two books with the same title. Now, your support programmers are going to have to deal with the fact that your data doesn't mean what you think it does. Ideally, at this point, you would realize that you need to add a table for authors, and you have the solution that will give you the results you want:

Now, if you need information about an author's relationship to a book (chapters written, pay rate, etc.) you can add columns to the second table without harming the current users of the system. Yes, you end up having more tables, and yes, you have to do more coding up front, but if you get the design right, it just plain works. The likelihood of discovering all data cases such as this case with multiple authors to a book before you have "completed" your design, is fairly low, so don't immediately think it is your fault. Requirements are often presented that are not fully thought through such as claiming only one author per book. Sometimes it isn't that the requirements were faulty so much as the fact that requirements change over time. In this example, it could have been that during the initial design phase the reality at the time was that the system was to support only a single author. Ever changing realities are what makes software design such a delightful task as times.

Clues That an Existing Design Is Not in First Normal Form

When you are looking at database design to evaluate it, you can look quickly at a few basic things to see whether the data is in First Normal Form. In this section, we'll look at some of these ways to recognize whether data in a given database is already likely to be in First Normal Form. None of these clues is, by any means, a perfect test. Generally speaking, they're only clues that you can look for in data structures for places to dig deeper. Normalization is a moderately fluid set of rules somewhat based on the content and use of your data.

The following sections describe a couple of data characteristics that suggest that the data isn't in First Normal Form:

• String data containing separator-type characters
• Column names with numbers at the end
• Tables with no or poorly defined keys

This is not an exhaustive list, of course, but these are a few places to start.

BCNF Defined

BCNF is named after Ray Boyce, one of the creators of SQL, and Edgar Codd, whom I introduced in the first chapter as the father of relational databases. It's a better-constructed replacement for both the Second and Third Normal Forms, and it takes the meanings of the Second and Third Normal Forms and restates them in a more general way. The BCNF is defined as follows:

• The table is already in First Normal Form.
• All columns are fully dependent on a key.
• A table is in BCNF if every determinant is a key.

Note that, to be in BCNF, you don't specifically need to be concerned with Second Normal Form or Third Normal Form. BCNF encompasses them both and changed the definition from the "primary key" to simply all defined keys. With today's almost universal de facto standard of using a surrogate key as the primary key, BCNF is a far better definition of how a properly designed database should be structured. It is my opinion that most of the time when someone says "Third Normal Form" that they are referencing something closer to BCNF.

An important part of the definition of BCNF is that "every determinant is a key." I introduced determinants back in Chapter 1, but as a quick review, consider the following table of rows, with X defined as the key:

X is unique, and given the value of X, you can determine the value of Y and Z. X is the determinant for all of the rows in the table. Now, given a value of Y, you can't determine the value of X, but you seemingly can determine the value of Z. When Y = 1: Z = 2 and when Y = 2: Z = 4. Now before you pass judgment, this could be a coincidence. It is very much up to the requirements to help us decide if this determinism is incorrect. If the values of Z were arrived at by a function of Y*2, then Y would determine Z and really wouldn't need to be stored (eliminating user editable columns that are functional dependent on one another is one of the great uses of the calculated column in your SQL tables, and they manage these sorts of relationships).

When a table is in BCNF, any update to a nonkey column requires updating one and only one value. If Z is defined as Y*2, updating the Y column would require updating the Z column as well. If Y could be a key, this would be acceptable as well, but Y is not unique in the table. By discovering that Y is the determinant of Z, you have discovered that YZ should be its own independent table. So instead of the single table we had before, we have two tables that express the previous table with no invalid functional dependencies, like this:

For a somewhat less abstract example, consider the following set of data, representing book information:

BookISBN is the defined key, so every one of the columns should be completely dependent on this value. The title of the book is dependent on the book ISBN, and the publisher too. The concern in this table is the PublisherLocation . A book doesn't have a publisher location, a publisher does. So if you needed to change the publisher, you would also need to change the publisher location.

To correct this situation, you need to create a separate table for publisher. Following is one approach you can take:

Now, a change of publisher for a book requires only changing the publisher value in the Book table, and a change to publisher location requires only a single update to the Publisher table.

Partial Key Dependency

In the original definitions of the normal forms, we had second normal form that dealt with partial key dependencies. In BCNF, this is still a concern when you have defined composite keys (with more than one column making up the key). Most of the cases where you see a partial key dependency in an example are pretty contrived (and I will certainly not break than trend). Partial key dependencies deal with the case where you have a multicolumn key and, in turn, columns in the table that reference only part of the key.

As an example, consider a car rental database and the need to record driver information and type of cars the person will drive. Someone might (not you, certainly!) create the following:

The key of driver plus vehicle style means that all of the columns of the table should reference both of these values. Consider the following columns:

• Height: Unless this is the height of the car, this references the driver and not car style.
• EyeColor : Clearly, this references the driver only, unless we rent Pixar car models.
• ExampleModel : This references the VehicleStyle , providing a model for the person to reference so they will know approximately what they are getting.
• DesiredModelLevel : This represents the model of vehicle that the driver wants to get. This is the only column that should be in this table.

To transform the initial one table into a proper design, we will need to split the one table into three. The first one defines the driver and just has the driver's physical characteristics:

The second one defines the car styles and model levels the driver desires:

And finally, we need one to define the types of car styles available and to give the example model of the car:

Note that, since the driver was repeated multiple times in the original poorly designed sample data, I ended up with only two rows for the driver as the Louis entry's data was repeated twice. It might seem like I have just made three shards of a whole, without saving much space, and ultimately will need costly joins to put everything back together. The reality is that in a real database, the driver table would be large; the table assigning drivers to car styles is very thin (has a small number of columns), and the car style table will be very small. The savings from not repeating so much data will more than overcome the overhead of doing joins on reasonably designed tables. For darn sure, the integrity of the data is much more likely to remain at a high level because every single update will only need to occur in a single place.

Entire Key Dependency

Third Normal Form and BCNF deal with the case where all columns need to be dependent on the entire key. (Third Normal Form specifically dealt with the primary key, but BCNF expanded it to include all defined keys.) When we have completed our design, and it is meets the standards of BCNF, every possible key is defined.

In our previous example we ended up with a driver table. That same developer, when we stopped watching, made some additions to the table to get an idea of what the driver currently drives:

To our trained eye, it is pretty clear almost immediately that the vehicle columns aren't quite right, but what to do? You could make a row for each vehicle, or each vehicle type, depending on how specific the need is for the usage. Since we are basically trying to gather demographic information about the user, I am going to choose vehicle type to keep things simple (and since it is a great segue to the next section). The vehicle type now gets a table of its own, and we remove from the driver table all of the vehicle pieces of information and create a key that is a coagulation of the values for the data in row:

And the driver table now references the vehicle type table using its key:

Note that for the vehicle type table in this model, I chose to implement a smart surrogate key for simplicity, and because it is a common method that people use. A short code is concocted to give the user a bit of readability, then the additional columns are there to use in queries, particularly when you need to group or filter on some value (like if you wanted to send an offer to all drivers of three-door cars to drive a luxury car for the weekend!) It has drawbacks that are the same as the normal form we are working on if you aren't careful (the smart key has redundant data in it), so using a smart key like this is a bit dangerous. But what if we decided to use the natural key? You would end up with two tables that look like this:

The driver table now has almost the same columns as it had before (less the WheelCount which does not differ from a three- or five-door hatchback, for example ), referencing the existing tables columns, but it is a far more flexible solution. If you want to include additional information about given vehicle types (like towing capacity, for example), you could do it in one location and not in every single row, and users entering driver information can only use data from a given domain that is defined by the vehicle type table. Note too that the two solutions proffered are semantically equivalent in meaning but have two different solution implementations that will have an effect on implementation, but not the meaning of the data in actual usage.

Dependency Between Rows

A consideration when discussing BCNF is data that is dependent on data in a different row, possibly even in a different table. A common example of this is summary data. It is an epidemic among row-by-row thinking programmers who figure that it is very costly to calculate values using set-based queries. So say, you have objects for invoice and invoice line items like the following tables of data, the first being an invoice, and the second being the line items of the invoice:

There are two issues with this data arrangement.

• InvoiceAmount is just the calculated value of SUM(Quantity * ProductPrice).
• InvoiceDate in the line item is just repeated data from Invoice.

Now, your design has become a lot more brittle, because if the invoice date changes, you will have to change all of the line items. The same is likely true for the InvoiceAmount value as well. However, it may not be so. You have to question whether or not the InvoiceAmount is a true calculation. In many cases, while the amounts actually seem like they are calculated, they may be there as a check. The value of 100 may be manually set as a check to make sure that no items are changed on the line items. The important part of this topic is that you must make sure that you store all data that is independent, regardless of how it might appear. When you hit on such natural relationships that are not implemented as dependencies, some form of external controls must be implemented, which I will talk about more in the later section titled "Denormalization".

There is one other possible problem with the data set, and that is the ProductPrice column. The question you have to consider is the life and modifiability of the data. At the instant of creating the order, the amount of the product is fetched and normally used as the price of the item. Of course, sometimes you might discount the price, or just flat change it for a good customer (or noisy one!), not to mention that prices can change. Of course, you could use the price on the order item row that is referenced. But you still might want to make changes here if need be.

The point of this work is that you have to normalize to what is actually needed, not what seems like a good idea when doing design. This kind of touch will be where you spend a good deal of time in your designs. Making sure that data that is editable is actually stored, and data that can be retrieved from a different source is not stored.

Clues That Your Database Is Not in BCNF

In the following sections, I will present a few of the flashing red lights that can tell you that your design isn't in BCNF.

• Multiple columns with the same prefix
• Repeating groups of data
• Summary data

Of course, these are only the truly obvious issues with tables, but they are very representative of the types of problems that you will frequently see in designs that haven't been done well (you know, by those people who haven't read this book!)

Positional Meaning

The last point I want to make about BCNF type issues is that you must be truly careful about the meaning of the data you are normalizing, because as you get closer and closer to the goal of one table having one meaning, almost every column will have one place where it makes sense. For example, consider the following table of data:

To get this table into First Normal Form, you should immediately recognize that we need to implement a table to hold the e-mail address for the customer. The questionable attribute is the AllowMarketingByEmailFlag, which denotes whether or not we wish to market to this customer by e-mail. Is this an attribute about the e-mail address? Or the customer?

Without additional knowledge from the client, it must be assumed that the AllowMarketingByEmailFlag column applies to how we will market to the customer, so it should remain on the customer table like this:

You will also notice that I made the key of the customer e-mail address table CustomerId , EmailAddressNumber and not EmailAddress . Without further knowledge of the system, it would be impossible to know if it was acceptable to have duplication in the two columns. It really boils down to the original purpose of having multiple EmailAddress values, and you have to be careful about what the customers may have been using the values for. In a situation I recently was working on, half of the users used the latter addresses as history of old e-mail addresses and the other half as a backup e-mail for contacting the customer. For the history e-mail address values, it certainly could make sense to add start and end date values to tell when and if the address is still valid, particularly for customer relationship management systems. But at the same time, it could make sense to have only current customer information in your OLTP system and move history of to an archival or data warehouse database instead.

Finally, consider the following scenario. A client sells an electronic product that is delivered by e-mail. Sometimes, it can take weeks before the order is fulfilled and shipped. So the designer of the system created the following three table solution (less the sales order line item information about the product that was ordered):

What do you figure the purpose is of the redundant e-mail address information? Is it a normalization issue? No, because although the ShippedToEmailAddress may be exactly the same as the e-mail address for the e-mail address table row with the related e-mail address number, what if the customer changed e-mail addresses and then called in to ask where you shipped the product? If you only maintained the link to the customer's current e-mail address, you wouldn't be able to know what the e-mail address was when it shipped.

The point of this section has been to think before you eliminate what seems like redundant data. Good naming standards, such as spelling out ShippedTo EmailAddress during the logical database design phase, are a definite help to make sure other developers/architects know what you have in mind for a column that you create.

Fourth Normal Form: Independent Multivalued Dependencies

Fourth Normal Form deals with what are called multivalued dependencies. When we discussed dependencies in the previous sections, we discussed the case where f(x) = y, where both x and y were scalar values. For a multivalued dependency, the y value can be an array of values. So f(parent) = (child1,child2, . . . , childN) is an acceptable multivalued dependency. For a table to be in Fourth Normal Form, it needs to be in BCNF first, and then, there must not be more than one independent multivalued dependency (MVD) between the key columns.

Recall a previous example table we used:

Think about the key columns. The relationship between Driver and VehicleStyle represents a multivalued dependency for the Driver and the VehicleStyle entities. A driver such as Louis will drive either CUV or Sedan style vehicles, and Louis is the only driver to drive the CUV style. As we add more data, each vehicle style will have many drivers that choose the type as a preference. A table such as this one for DriverVehicleStyle is used frequently to resolve a many-to-many relationship between two tables, in this case, the Driver and VehicleStyle tables.

The modeling problem comes when you need to model a relationship between three entities, modeled as three columns in a key from three separate table types. As an example, consider the following table representing the assignment of a trainer to a class that is assigned to use a certain book:

To decide if this table is acceptable, we will look at the relationship of each column to each of the others to determine how they are related. If any two columns are not directly related to one another, there will be an issue with Fourth Normal Form with the table design. Here are the possible combinations and their relationships:

• Class and Trainer are related, and a class may have multiple trainers.
• Book and Class are related, and a book may be used for multiple classes.
• Trainer and Book are not directly related, because the rule stated that the class uses a specific book.

Hence, what we really have here are two independent types of information being represented in a single table. To deal with this, you will split the table on the column that is common to the two dependent relationships. Now, take this one table and make two tables that are equivalent:

Joining these two tables together on Class, you will find that you get the exact same table as before. However, if you change the book for the Normalization class, it will be changed immediately for both of the classes that are being taught by the different teachers. Note that initially it seems like we have more data because we have more rows and more tables. However, notice the redundancy in the following data from the original design:

The redundancy comes from stating twice that the book DB Design & Implementation is used for the Normalization class. The new design conveys that same information with one less row of data. Once the system grows to the size of a full-blown system that has 50 Normalization classes being taught, you will have much less data, making the storage of data more efficient, possibly affording some performance benefits along with the more obvious reduction in redundant data that can get out of sync.

As an alternate situation, consider the following table of data, which might be part of the car rental system that we have used for examples before. This table defines the brand of vehicles that the driver will drive:

• Driver and VehicleStyle are related, representing the style the driver will drive.
• Driver and VehicleBrand are related, representing the brand of vehicle the driver will drive.
• VehicleStyle and VehicleBrand are related, defining the styles of vehicles the brand offers.

This table defines the types of vehicles that the driver will take. Each of the columns has a relationship to the other, so it is in Fourth Normal Form. In the next section, I will use this table again to assist in identifying Fifth Normal Form issues.

Fifth Normal Form

Fifth Normal Form is a general rule that breaks out any data redundancy that has not specifically been culled out by additional rules. Like Fourth Normal Form, Fifth Normal Form also deals with the relationship between key columns. Basically, the idea is that if you can break a table with three (or more) independent keys into three (or more) individual tables and be guaranteed to get the original table by joining them together, the table is not in Fifth Normal Form

Fifth Normal Form is an esoteric rule that is only occasionally violated, but it is interesting nonetheless because it does have some basis in reality and is a good exercise in understanding how to work through intercolumn dependencies between any columns. In the previous section, I presented the following table of data:

At this point, Fifth Normal Form would suggest that it's best to break down any existing ternary (or greater!) relationship into binary relationships if at all possible. To determine if breaking down tables into smaller tables will be lossless (that is, not changing the data), you have to know the requirements that were used to create the table and the data. For the relationship between Driver, VehicleStyle and VehicleBrand, if the requirements dictate that the data is that

• Louis is willing to drive any Station Wagon or Sedan from Ford or Hyundai.
• Ted is willing to drive any Coupe from Chevrolet.

Then, we can infer from this definition of the table that the following dependencies exist:

• Driver determines VehicleStyle.
• Driver determines VehicleBrand.
• VehicleBrand determines VehicleStyle.

The issue here is that if you wanted to express that Louis is now willing to drive Volvos, and that Volvo has station wagons and sedans, you would need to add least two rows:

In these two rows, you are expressing several different pieces of information. Volvo has Station Wagons and Sedans. Louis is willing to drive Volvos (which you have repeated multiple times). If other drivers will drive Volvos, you will have to repeat the information that Volvo has station wagons and sedans over and over.

At this point, you probably now see that ending up with tables with redundant data like in our previous example is such an unlikely mistake to make—not impossible, but not probable by any means, assuming any testing goes on with actual data in your implementation process. Once the user has to query (or worse yet, update) a million rows to express a very simple thing like the fact Volvo is now offering a sedan class automobile; changes will be made. The fix for this situation is to break the table into the following three tables, each representing the binary relationship between two of the columns of the original table:

I included the additional row that says that Louis will drive Volvo vehicles and that Volvo has station wagon and sedan style vehicles. Joining these rows together will give you the table we created:

I mentioned earlier that the meaning of the table makes a large difference. An alternate interpretation of the table could be that instead of giving the users such a weak way of choosing their desired rides (maybe Volvo has the best station wagons and Ford the best sports car), the table just presented might be interpreted as

• Louis is willing to drive Ford station wagons, Hyundai sedans, and Volvo station wagons and sedans.
• Ted is willing to drive a Chevrolet coupe.

In this case, the table is in Fifth Normal Form because instead of VehicleStyle and VehicleBrand being loosely related, they are directly related and more or less to be thought of as a single value rather than two independent ones. Now the dependency is Driver to VehicleStyle plus VehicleBrand. In a well-designed system, the intersection of style and brand would have formed its own table because VehicleStyle/VehicleBrand would have been recognized as an independent object with a specific key often a surrogate key which represented a VehicleBrand that was rentable. However, in either case, the logical representation after decoding the surrogate keys would, in fact, look just like our table of Driver, VehicleStyle, and VehicleBrand.

As our final example, consider the following table of Books along with Authors and Editors:

There are two possible interpretations that would hopefully be made clear in the name of the table:

• This table is in not even in Fourth Normal Form if it represents the following:
  • The Book Design has Authors Louis and Jeff and Editors Jonathan and Leroy.
  • The Book Golf has Authors Louis and Fred and Editors Steve and Tony.
• Table is in Fifth Normal Form if it represents
  • For the Book Design, Editor Jonathan edits Louis' work and Editor Leroy edits Jeff's work.
  • For the Book Golf, Editor Steve edits Louis' work, and Editor Tony edits Fred's work.

In the first case, the author and editor are independent of each other, meaning that technically you should have a table for the Book to Author relationship and another for the Book to Editor relationship. In the second case, the author and editor are directly related. Hence, all three of the values are required to express the single thought of "for book X, editor Y edits Z's work."

What can be gleaned from Fourth and Fifth Normal Forms, and indeed all the normal forms, is that when you think you can break down a table into smaller parts with different natural keys, which then have different meanings without losing the essence of the solution you are going for, then it is probably better to do so. If you can join the parts together to represent the data in the original less-broken-down form, your database will likely be better for it. Obviously, if you can't reconstruct the table from the joins, leave it as it is. In either case, be certain to test out your solution with many different permutations of data. For example, consider adding these two rows to the earlier example:

If this data does not mean what you expected, then it is wrong. For example, if as a result of adding these rows, users who just wanted Volvo sedans were getting put into station wagons, the design would not be right.

Last, the point should be reiterated that breaking down tables ought to indicate that the new tables have different meanings. If you take a table with ten nonkey columns, you could make ten tables with the same key. If all ten columns are directly related to the key of the table, then there is no need to break the table down further.