Normalization in an ER Diagram: Fix a Repeating Group (First Normal Form, Visio)
Phone1, Phone2, Phone3 in one table is a repeating group, and first normal form says take it out. Learn how to move it into its own entity with a foreign key, drawn in Visio with crow's foot notation.
Step by step
- Repeating group. Phone1, Phone2 and Phone3 in one table is called a repeating group. First normal form says to take it out. Here is the fix, drawn as an ER diagram in Visio.
- Crow's Foot stencil. The diagram uses the Crow's Foot Database Notation stencil in the Shapes pane.
- The problem. Start with a Contact entity that has 3 attributes: ContactID, Name and Email. So far each attribute holds one value per contact. It's tempting to add Phone1, Phone2 and Phone3 under it. That's a repeating group. 3 slots may not be enough, most of the cells stay empty, and finding a number means searching 3 columns.
- Own entity. The fix is to give phones their own entity. Add a Phone entity with PhoneID, ContactID and Number. Each phone number is now one row.
- Connect them. Connect Contact to Phone with a Relationship connector. In crow's foot notation the ends read like this: one contact has zero or many phones, and each phone belongs to one and only one contact.
- The foreign key. ContactID inside Phone is the foreign key. It points back to the contact the phone belongs to, and that one column replaces Phone1, Phone2 and Phone3.
- Rows, not columns. Now a contact can have 1 phone or 10. A new number means a new row, never a new column. To tell mobile numbers from work numbers, add a Type attribute to Phone, next to Number. It's still one row per phone.
- Done. Click an empty part of the page to clear the selection and see the result: 2 linked entities and no repeating group, so the design is in first normal form.
- The rule. The rule: when the same column is numbered 1, 2, 3, it's a repeating group. Move it into its own entity, link it back with a foreign key, and add rows instead of columns.
Common mistakes
- Leaving the foreign key out of the new entity. A Phone entity with only PhoneID and Number can't tell you whose phone it is. Add ContactID to Phone so every row points back to one contact.
- Putting the key on the wrong side. If you add PhoneID to Contact instead of ContactID to Phone, each contact can still have only one phone. The foreign key goes on the many side, which here is Phone.
- Renaming the columns instead of removing them. MobilePhone, WorkPhone and HomePhone are still a repeating group with nicer names. Use one Number column and one Type column in the Phone entity.
- Putting several numbers in one cell. A single Phone column holding "0412 555 111, 02 5550 1234" also breaks first normal form, because the cell holds more than one value. Store one number per row in Phone.
When a business analyst uses this
In a CRM requirements workshop, the stakeholders' spreadsheet has Phone1, Phone2 and Phone3 columns. Drawing Contact and Phone as separate entities in Visio, joined by ContactID, shows the developers the data rule before sign-off: a contact can have any number of phones, each with a type.
Questions people ask
What is normalisation in an ER diagram?
It means arranging entities and attributes so each fact is stored once, in the right place. Moving a repeating group like Phone1, Phone2 and Phone3 into its own entity is the first step, called first normal form.
What is an entity in an ER diagram?
An entity is a thing you store data about, like Contact or Phone. It usually becomes a database table, and its attributes, like ContactID and Name, become the columns.
What is 3NF in normalisation?
Third normal form builds on first and second normal form. Every non-key column must depend only on the table's key, not on another non-key column. If Contact stored CompanyID and CompanyName, CompanyName would move to a Company entity.
