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:
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.
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:
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.