COMMON DATA MODELING PROBLEMS - A Developer's Guide to Data Modeling for SQL Server - page 172

F IGURE 8.1

The original Customers entity for Mountain View Music

Notice the seemingly duplicate address data. In the strictest sense of
the word this data isn't really duplicate data—it contains work information
versus home information—but the type of data is redundant. We were told
during requirements gathering that Mountain View needed to store at least
two addresses for each customer and that the home and the work addresses
were the most common addresses on file. Storing the data in the way that
we have in Figure 8.1 presents a few problems. The first problem is that
the model is not flexible. If we need to store additional addresses later, we
would not be able to do so without first modifying the entity to add
columns. Second, the data is difficult to retrieve in this state. Applications
would need to be written to understand the complexity and pull data from
the correct columns. This problem is compounded by the changes that
would need to be made to the application if we later add a third address.
This is a clear example of having too few entities, and we can tell that
by the duplication of information. The fix here is to give the duplicate data
its own entity and establish a relationship with the original entity. In Figure
8.2 we have split the address data into its own entity.