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. Phone one, phone two, phone three. You've seen that in a spreadsheet. In a database it's called a repeating group, and first normal form says take it out. Let's fix it in Visio.
- Crow's Foot stencil. I'm using the Crow's Foot Database Notation stencil.
- The problem. Here's a contact entity. Contact ID, a name, and an email. Clean so far. The temptation is to add phone one, phone two and phone three under it. That's a repeating group. Three is never enough, most cells stay empty, and finding a number means searching three columns.
- Own entity. The fix. Give phones their own entity. Phone ID, the contact it belongs to, and the number. One row per phone.
- Connect them. Connect them. One contact has zero or many phones. Each phone belongs to one and only one contact.
- The foreign key. Contact ID inside phone is the foreign key. That one column replaces the three phone columns.
- Rows, not columns. Now a contact can have one phone or ten. You add a row, never a column. Need to know which is mobile and which is work? Add a type column here, next to the number. Still one row per phone.
- Done. Click away to see the result. First normal form, no repeating group.
- The rule. So, when you see the same column numbered one, two, three, that's a repeating group. Move it to its own entity, link it back with a foreign key, and let the rows grow instead.
