How to Normalize an ERD

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.
Entity relationship diagrams help organize data.
Image Credit: Comstock/Comstock/Getty Images

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.

Advertisement

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:

Video of the Day

Customer Entity Customer ID Number, Name, Address, City, State, Zip, Comment 1, Comment 2, Comment 3, Total Sales, Salesman 1, Salesman 2, Salesman 3.

Advertisement

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:

Advertisement

Customer Entity Customer ID Number, Name, Address, City, State, Zip, Total Sales.

Comments Entity Comment

Salesmen Entity Salesman

Advertisement

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:

Advertisement

Customer Entity Customer ID Number, Name, Address, City, State, Zip, Total Sales.

Comments Entity Customer ID Number, Comment

Salesmen Entity Customer ID Number, Salesman

The ERD now accommodates any number of comments or salesmen for any given customer.

Video of the Day

Advertisement

Advertisement

references