Normalizing Tables and Relationships

My EBook Companion database started life as a quick-and-dirty database to store information about downloaded ebooks, for my personal use. I guess this is a case of the shoemaker’s children going barefoot, because this database was definitely not normalized! The original tblEBooks table has two sets of Author fields (first, middle, and last names for author and coauthor) and the Title field is the key field, as shown in Figure 9.16.

Figure 9.16

As I added more and more ebooks to the database, I found that this non-normalized structure wouldn’t work, and not just for theoretical considerations—some books I needed to add to the database had more than two authors, and there were different books with the same name. The database needed to be normalized to avoid these problems. Working through the process of normalizing this database should give you a good idea of what is involved in normalizing a client database that you have to rework.

For tblEBooks, ISBN would be the obvious choice as a unique ID for books. But the ISBN isn’t generally available at the time ebooks are downloaded, so I just added a BookID AutoNumber field to tblEBooks, to give each book a unique ID—that was easy!

The next step was more complicated—authors (as many as are needed for each book) had to be moved into a separate table, linked on BookID, and author data needed its own separate table, with one record for each author, to avoid the duplicate author data in the original tblEBooks. A book can have multiple authors and an author can write many books, so the tblEBooks table and tblAuthors table should be linked many-to-many through the linking table, tblEBookAuthors.

The Original EBook Companion database has the old, non-normalized tables; the EBook Companion database has the normalized tables and modified forms and reports, after all the changes discussed in the following sections have been made.

To start, I moved the author data from the old, non-normalized tblEBooks table into new, normalized tables using several queries. First, I made qryAuthors, containing the AuthorFirstName, AuthorMiddleName, and AuthorLastName fields from tblEBooks, and I set the query’s Unique Values property to Yes to eliminate duplicates. I made a similar query (qryCoauthors) using the CoauthorFirstName, CoauthorMiddleName, and CoauthorLastName fields, with the addition of a criterion of Is Not Null on the CoauthorLastName field, so the query’s results contained only records that had coauthor data.

Next, I constructed a union query (quniAuthors) to join the author and coauthor data into a single recordset, using the following SQL statement:

 SELECT qryAuthors.AuthorLastName AS LastName, qryAuthors.AuthorFirstName  AS FirstName, qryAuthors.AuthorMiddleName AS MiddleName FROM qryAuthors UNION SELECT qryCoauthors.CoauthorLastName AS LastName,  qryCoauthors.CoauthorFirstName AS FirstName, qryCoauthors.CoauthorMiddleName  AS MiddleName FROM qryCoauthors; 

So that I could tinker with the author names without affecting the data in tblEBooks (not absolutely necessary, but a good idea), I made a make-table query (qmakAuthors), sorted by last name, first name, and middle name (this query is shown in Design view in Figure 9.17). The table made by this query is called tmakAuthors.

Figure 9.17

I use the tmak prefix to match up a table with the make-table that created it, so I know at a glance which tables in the database window were created by make-table queries.

I did a little clean-up on the resulting table (fixed a few typos in author names, removed some duplicates that snuck in despite setting Unique Values to True, moved a “Jr.” into a Suffix field I added to the table), and then I made a new, empty table (tblAuthors) to hold the cleaned-up author data, and an append query (qappAuthors) to move the data from tmakAuthors into tblAuthors. The qappAuthors query is shown in Figure 9.18.

Figure 9.18

The new tblAuthors table has an AuthorID AutoNumber field. A portion of tblEBookAuthors is shown in Figure 9.19.

Figure 9.19

The final step in normalizing the tables is to create the linking table, tblEBookAuthors. This table sets up the many-to-many relationship between tblEbooks and tblAuthors. In Access, a many-to-many relationship is actually two one-to-many relationships, with the linking table being on the “many” side of both relationships. A many-to-many relationship is needed here because there are (potentially) many records in tblEBookAuthors for one ebook, or one author, because an author can write many books, and a book can have more than one author.

Figure 9.20

tblEBookAuthors needs only two fields, AuthorID and BookID; these fields are the key fields of tblAuthors and tblEBooks, respectively, and the foreign keys in the one-to-many relationships between tblAuthors and tblEBookAuthors, and tblEBooks and tblEBookAuthors. Figure 9.20 shows the one-to-many relationship being set up between tblAuthors and tblEBookAuthors; the other relationship is done in a similar manner.

The two finished relationships are shown in the Relationships window in Figure 9.21.

Figure 9.21

The final step of the normalizing process is the most complex: filling tblEBookAuthors with data linking authors to their books. This requires matching up the new BookID field in tblEBooks with the appropriate AuthorID in tblAuthors, using the old author data fields to find the author in tblAuthors. To facilitate this matching, I made two queries that combine the author first, middle. and last name components into a single AuthorName field. qryEBooksWithAuthorNames is shown in Design view in Figure 9.22, and in Datasheet view in Figure 9.23.

Figure 9.22

Figure 9.23

Next, I made a query (qryAuthorNames) based on tblAuthors, containing the AuthorID field and a similar AuthorName concatenated field. I then joined the two queries on the AuthorName field in qryEBooksAndAuthors, linking the tables with a LEFT JOIN, so that there would be one record for each record in tblEBooks. This query is shown in Design view in Figure 9.24.

Figure 9.24

After checking the query in Datasheet view (as shown in Figure 9.25), I made a similar query for the coauthor names (qryEBooksAndCoauthors) and a union query to combine their data. This is necessary, because otherwise there would be only one record in tblEBookAuthors for each book, and the coauthor data would be lost.

Figure 9.25

The union query that combines author and coauthor book data is shown in Figure 9.26.

Figure 9.26

I made an append query based on the union query, linked to tblAuthors on the AuthorName field, renamed it with the qapp prefix, and ran the qappEBooksAndAuthors query to fill tblEBookAuthors. This query is shown in Design view in Figure 9.27.

Figure 9.27

A portion of tblEBookAuthors is shown in Figure 9.28.

Figure 9.28