You may remember our initial design for an eCommerce database that we 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, we 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 we 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.
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.
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:
We 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 we have the billingAddressID and shippingAddressID. We 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, we would not know which address was being used for each. Having the direct link to the address table avoids this problem.
You will also notice that we have the unitPrice set on the OrderLine table to indicate the price that was used for the order. We will also store the orderTotal in the Order table as there could potentially be a discount used later on (which we have not identified). The relationship between the product and category is a many-to-many relationship so we 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.
Source: Authored by Vincent Tran