Normalizing Repeated Columns: Single Repeated Column(Part1)

In my earlier series (The Problem of Repeated Columns), I defined repeated columns and talked about the data integrity problems associated with them. I also showed several different examples of repeated columns and the how difficult it is to query repeated columns as compared to the normalized equivalent. If you haven't read these yet, it would be worthwhile to read first. Similarly, if you are not familiar with the concept of Normalization, you should read my blog series What is Normalization?

One of the comments suggested I should discuss how to convert a table with repeated columns into a normalized table structure. I thought that was a really good idea, so I'm going to spend the next few posts doing so.

The difficulty in discussing this issue, however, is that the exact solution differs with each table, so there is no single solution. Fortunately, in the last series, I showed 5 different tables, each with a slightly different structure, so while I cannot show a single solution that will work for every table with repeated columns, hopefully, one of the following will work for most cases.

Others in this series:

• Single Repeated Column  (this post)
• Multiple Columns
• Normalizing Yes/No Fields 
• Using VBA

Simple PC Inventory

In my post Querying Repeated Columns: Multiple ORs I discussed an extremely simple example of a PC Inventory table that looked like this:

Figure 1: PC_Inventory table with repeated columns

Normalized to remove the repeated columns, the tables would look like this:

Figure 2: PC_Inventory normalized (First Normal Form (1NF)

That was sufficient for illustrating the difficulty of querying repeated columns, but unfortunately it didn't go far enough. Proper normalization requires that, in addition to moving the PC_Num and OperatingSystem fields to their own table, Software should also be removed to its own table as well. So it really should look like this:

Figure 3: PC_Inventory fully normalized (Third Normal Form (3NF)

The table relationships would look like this:

Figure 4: Relationships for the PC Inventory tables.

So how do I get the data from Figure 1 to Figure 3? Well, first of all, it depends on whether this is a one-time process where you are creating new normalized tables, or whether it is an on-going process where you are appending the data to existing, normalized tables.

In either case, however, not only do I have to split the records into separate tables, I also have to preserve the relationships between those records, so I have to do it is a specific order.

One-Time Process

Suppose I'm given a spreadsheet of data like this:

And I need to create a normalized database from it. I've already determined the tables I need (Figure 3 above), but if you're not certain what your table structure should be, you should read through my blog serie: What is Normalization, Entity-Relationship Diagramming, and The Normal Forms.


Link the spreadsheet into a database.

First of all, I need to get the spreadsheet into the database. I find it is preferable to link rather than import the file. Since I'm not going to be changing the data, there's no reason to import it. Once the file is linked it acts just like a table, and I can begin the process of normalizing it.

Step 1: Remove the common PC elements.

Since I need to create a new table to hold these values, I'll use a Make-Table query:

SELECT PC_Num, OperatingSystem INTO PC
FROM PC_RC_Link;

Or in the Query Builder:

The resulting table looks like this:

Now, if I were using PC_NUM as a natural key, I could stop here. However, I prefer to use Surrogate Keys. It makes this process more difficult, but has many benefits in the long run. (For a discussion of natural vs. surrogate keys, see my blog post: What is a Primary Key?)

Add surrogate key to PC table.

Adding a surrogate key to a table is as simple as opening it in Design View, adding an Autonumber Field, and making it the Primary Key.

Saving the table will automatically fill the autonumber field with values:

Step 2: Create Software_Temp table

As I showed in my post Querying Repeated Columns: Multiple Unions it is necessary to use multiple UNION queries to create a list of Software. Something like this:

SELECT PC.PC_ID, PC_RC_Link.Software1 As Software
FROM PC INNER JOIN PC_RC_Link ON PC.PC_Num = PC_RC_Link.PC_Num
WHERE Software1 IS NOT NULL
UNION
SELECT PC.PC_ID, PC_RC_Link.Software2
FROM PC INNER JOIN PC_RC_Link ON PC.PC_Num = PC_RC_Link.PC_Num
WHERE Software2 IS NOT NULL
UNION
SELECT PC.PC_ID, PC_RC_Link.Software3
FROM PC INNER JOIN PC_RC_Link ON PC.PC_Num = PC_RC_Link.PC_Num
WHERE Software3 IS NOT NULL
UNION
SELECT PC.PC_ID, PC_RC_Link.Software4
FROM PC INNER JOIN PC_RC_Link ON PC.PC_Num = PC_RC_Link.PC_Num
WHERE Software4 IS NOT NULL
UNION
SELECT PC.PC_ID, PC_RC_Link.Software5
FROM PC INNER JOIN PC_RC_Link ON PC.PC_Num = PC_RC_Link.PC_Num
WHERE Software5 IS NOT NULL

Note: You can't create or view this query in the Query Builder, however, you can create the first on (Software1) in the QB, then switch to the SQL View and copy and paste, modifying each for the specific column.

I still need to make this query into a Make-Table query. To do that, I surround the above in parentheses and use it in the From clause of the Make-table. Like this:

SELECT PC_ID, Software INTO Software_Temp
FROM
(SELECT PC.PC_ID, PC_RC_Link.Software1 As Software
FROM PC INNER JOIN PC_RC_Link ON PC.PC_Num = PC_RC_Link.PC_Num
WHERE Software1 IS NOT NULL
UNION
SELECT PC.PC_ID, PC_RC_Link.Software2
FROM PC INNER JOIN PC_RC_Link ON PC.PC_Num = PC_RC_Link.PC_Num
WHERE Software2 IS NOT NULL
UNION
SELECT PC.PC_ID, PC_RC_Link.Software3
FROM PC INNER JOIN PC_RC_Link ON PC.PC_Num = PC_RC_Link.PC_Num
WHERE Software3 IS NOT NULL
UNION
SELECT PC.PC_ID, PC_RC_Link.Software4
FROM PC INNER JOIN PC_RC_Link ON PC.PC_Num = PC_RC_Link.PC_Num
WHERE Software4 IS NOT NULL
UNION
SELECT PC.PC_ID, PC_RC_Link.Software5
FROM PC INNER JOIN PC_RC_Link ON PC.PC_Num = PC_RC_Link.PC_Num
WHERE Software5 IS NOT NULL)

The result will be a table that looks like this:

If I added a surrogate key to this table, it would fulfill normalization to the First Normal Form as in Figure 1 above. However, I want to normalize it to 3NF. To do that, I still need to remove the duplicates from Software_Temp and create the linking table, PC_Software.

Step 3: Remove Duplicates from Software_Temp

I need to create a query that removes the duplicate values. The DISTINCT predicate works well for that. As before, I'll use a Make-Table query to create a new table to hold the software values:

SELECT DISTINCT Software INTO Software
FROM Software_Temp;

And as before, the PC table I created, I'll add an Autonumber primary key, so the table ends up like this:

Step 4: Creating the Linking Table: PC_Software

So now, I've got my two "One-Side" tables: PC and Software. Now I just need to create the linking table.

A "Linking table" is a mechanism by which Many-To-Many relationships are built in a relational database. (See: What is Normalizion Part V for more information.)

I can do that with a Make-Table Query and a simple join of Software and Software_Temp:

SELECT Software.SoftwareID, Software_Temp.PC_ID INTO PC_Software
FROM Software INNER JOIN Software_Temp ON Software.Software = Software_Temp.Software;

Or in the Query Builder:

This will give me a table with two fields: PC_ID and SoftwareID. I'll also want to make these fields a compound primary key:

The final result looks like this:

The final step is to create the relationships between the three tables: PC, PC_Software, and Software.

Step 5: Create the Relationships

The easiest way to create relationships in Access is to use the Relationship Window. Add the three tables to the Relationships Window:

Click and drag PC_ID from the PC table to PC_ID in the PC_Software table. In the pop-up window, choose the Enforce Referential Integrity box:

And click Create. Do the same for SoftwareID between Software and PC_Software. The final result will look like this:

Next Time:

Next time, I’ll finish up by importing the spreadsheet to existing tables:
Normalizing Repeated Columns: Single Repeated Column (Part2)