Online College Courses for Credit

+
ERD Example:  Complexity

ERD Example: Complexity

Rating:
(0)
Author: Sophia Tutorial
Description:

Recognize a overly complex ERD that could be optimized.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores the issues with an overly complex ERD.

Let us look back at the database design that we had in the prior tutorial as that will help us review some of the complexity in the data models:

File:11542-5330-1.png

If we have an organization that only has a billing and shipping address and just allows a single phone number, does it make sense for the organization to have this level of complex organization? We have to determine this early on as the more complex a database design is, the more work it is not only to join the data but create the underlying application code to make use of these tables. A lot more validation, checks, has to go in to manage and provide the information on the data to be more consistent. Let us compare just the customer part with the address and phone numbers between the complex and simple data model given the business rules we have defined. You will see that in the complex design, the customer table is simpler with separate tables to list their phone numbers and addresses allowing as many values as possible. In reality, most customers only store a single shipping address, billing address, and phone number for a vendor.

File:11543-5330-2.png

There can certainly be exceptions and this type of format will account for that. However, for a simpler scenario where there is not as much repeat business, a single table could do similar to the following:

File:11544-5330-3.png

This in many scenarios will be dependent on the business rules within the organization of how we define the database. If we focus on having full flexibility, we will introduce a lot more complexity not only in the database design but for all other components that link to the database. The queries, reports, and application code will be simpler to create with a simpler data model if it is warranted. You will see this type of structure to simplify the data model. For example, although in 3NF you would typically split off the zip code, city, and state in its own table, for simplicity with reporting and functionality, it’s commonly just included in the same table as the rest of the address.



summary
Having overly complex database designs can create a lot more work with the database design, queries, and underlying application code to interact with the database.