A repeating group is a series of fields/attributes that are repeated throughout a database table. It is a common problem faced by organizations both large and small, one that can have several ramifications. For example, the same set of information being present in different areas can cause data redundancy and data inconsistency. Moreover, all of this repeating data can eat up a lot of valuable disk space and take a long of time to search through. The problem of repeating groups can be manageable in small organizations, but for larger organizations, whom must manage huge volumes of information, repeating groups can become a nightmare to deal with.
In today's blog, we'll learn how to identify repeating groups both during design time and in existing databases, as well as how to fix them. Since repeating groups are a phenomenon that can affect any relational database, we'll use Navicat Premium as our database development tool.
An Example of a Repeating Group
The Sakila sample database contains a number of database entities relating to a fictional video rental store. Although its tables have been normalized to Third Normal form (3NF), for the purposes of this tutorial, we'll consider that the film table contains data about actors who appear in each film. Here is a sampling of rows from that table:
You can see that each actor is adding an extra row to the table. Worse still, actors' names are repeated every time that they come up. The problem is that an actor is a separate and distinct entity from a film. Hence, they need to go.
Fixing Repeating Groups
Even though repeating groups are not, strictly speaking, a violation of first normal form (1NF), the process of converting your data from Un-Normalized Form (UNF) to 1NF will eliminate repeating groups. Here are the steps for doing that:
- Identify the repeating groups of data.
- Remove the the repeating group fields to a new table, leaving a copy of the primary key with the table that is left.
- The original primary key will not now be unique so assign a new primary key to the relation using the original primary key as part of a composite key.
Since we've already identified the repeating groups, let's re-design the table so that repeating group fields are omitted and given their own table.
Navicat Premium comes with a built-in Data Modeler. It helps you visually design high-quality conceptual, logical and physical data models. From there, you can generate database structures from a model. The Data Modeler also works in reverse, performing reverse engineering from existing databases. Other features include import from ODBC data sources, generate complex SQL/DDL, and print models to files.
Here is a model showing the existing films_and_actors table:
To separate actors from films, we need to add a new table to host the actor attributes. We should also give it an ID PK field that will link to the same (new FK) field in the original table.
You'll also want to rename tables to reflect that the films table only contains films and actors only stores actor information.
Linking the films and actors Tables
How you link the tables together will depend on their particular relationship to each other. In this case, a film may have zero or more actors, and actors may appear in one or more films. Such a many-to-many relationship will require an intermediary table to link films and actors. It will contain only film and actor IDs. Here is the completed model in the Navicat Modeler:
Conclusion
In today's blog we learned how to identify repeating groups both during design time and in existing databases, as well as how to fix them, using Navicat Premium's powerful Data Modeler. Navicat Premium adds over 100 enhancements and includes several new features to give you more ways that ever to build, manage, and maintain your databases than ever before!
