ERD Example: eCommerce

1. Introduction

You may remember our initial design for an eCommerce database that you had created in a prior tutorial and also includes some potential issues with the data. Let us explore the database and see what potential issues there are:

Given that this is the starting database, if the customer has different addresses for the shipping and billing address, you are not able to distinguish this. Right now, the user can only have a single address for shipping and billing and cannot store multiple addresses. The user would only have a single phone number and you cannot distinguish the type of phone whether it is an office, cell phone, landline, or even a fax number. Similarly, the user can only have a single credit card.

think about it

From an order perspective, if the product’s price changes at any given point, there is no way to indicate what the customer had purchased a product for at that given time. A product could also belong to multiple categories but with this setup, you could only have a product belong to a single category.

2. Adding Details

Let us see a data model that would help resolve these problems but as you can see, this becomes a lot more complex of a design:

You have the AddressType and PhoneType setup as lookup tables. For example, the PhoneType will have values in the name like Work, Mobile, Home, and Fax. The AddressType would have values in the name like Billing and Shipping. You may notice that you have the billingAddressID and shippingAddressID. You need this link between the tables to avoid the chasm trap. Without having those values in place, if the customer has multiple billing or shipping addresses, you would not know which address was being used for each. Having a direct link to the address table avoids this problem.

You will also notice that you have the unitPrice set on the OrderLine table to indicate the price that was used for the order. You will also store the orderTotal in the Order table as there could potentially be a discount used later on (which you have not identified). The relationship between the product and category is a many-to-many relationship so you have created a ProductCategory table to map that relationship. This complete ERD should be a more complete structure for the eCommerce database but you still need to consider any business rules and criteria.