How to Normalize an ERD

By J.T. Barett

Database administrators and software developers use Entity Relationship Diagrams to organize a database’s design. The diagram specifies major groups of information and specific attributes such as name, telephone number and address. Sometimes, data in a major group comes in multiples; for example, a sales order may have one line item, five or 20. In your database design, you could accommodate this by creating 20 line items for every order, but this wastes hard drive space and doesn’t handle 21 items. The solution is to store repeated data as separate entities. Computer scientists call this process normalization. When you normalize an ERD, the results are flexible and efficient.

Step 1

Read through an entity’s attributes. Note those that repeat. Of those that repeat, identify and group similar attributes. For example, a customer entity may have the following attributes:Customer EntityCustomer ID Number, Name, Address, City, State, Zip, Comment 1, Comment 2, Comment 3, Total Sales, Salesman 1, Salesman 2, Salesman 3.The Comment and Salesman attributes repeat.

Step 2

Remove the repeating attributes from the original entity. Group them and create new entities for them. For the above example, create new entities, “Comments” and “Salesmen.” You now have the following entities and attributes:Customer EntityCustomer ID Number, Name, Address, City, State, Zip, Total Sales.Comments EntityCommentSalesmen EntitySalesman

Step 3

Add an attribute to the new entities that connects them to the original entity. The new entities don’t stand on their own -- they serve as subsidiaries to the original. The link uniquely identifies each record in the original entity. For example, the Customer ID Number uniquely identifies each customer. This normalizes the original ERD as follows:Customer EntityCustomer ID Number, Name, Address, City, State, Zip, Total Sales.Comments EntityCustomer ID Number, CommentSalesmen EntityCustomer ID Number, SalesmanThe ERD now accommodates any number of comments or salesmen for any given customer.