Okay, so I have seen some organizations value the Dell DVD Store for benchmarks. It is very simple in its form (very simple I must say), so it is easy to grasp what this Dell DVD Store is all about within a day or two. I only looked at the database structure and not the website structure. The thing is the database structure is all you need. The database has a lot of power. This database in summary has a lot limitations for the future flexibility of the website, but has some key advantages within its current structure. The DELL DVD Store project is open source, so you can do anything you want with it, as long you do not directly derive money out of it. I've downloaded "ds21_mysql.tar.gz" and looked at the files of the "build" folder. Let us see more in detail a general picture of the database tables bellow:
Okay, so it is pretty much a lot of stuff to cover. Let us overview the first half then.
CUSTOMERS TABLE
The first table that we are going to look (and most likely the most important one) is the Customers table. Before looking details of the customer, let us notice that the "build" folder has set a procedure that tells us when it is allowed and when it is not allowed to input a record inside the customer table (filename "mysqlds2_create_stp.sql"). The stored procedure has one output parameter, the customer_id. This is probably used on the web server to determine if the account was created successfully or not (If it returns 0, it will say "sorry, username is already used"). Username? I never mentioned about username. Well, yes, yes, the determination of whether a customer account can be inserted in the customers table is based if the login is already used. What does that mean? Based on the current behavior, it means the user name must be unique in the customers table (only one record). This translates that the table is normalized, but has a lot of limitations. Let me explain:
- ADDRESS INFO ( FIRSTNAME,LASTNAME,ADDRESS1,ADDRESS2,CITY,STATE, ZIP,COUNTRY,REGION): One user can only have one address. Is this normal for a DVD online store? Definitely not. What if I am trying to send a gift to a stranger? Is it possible? Can I use the same address for billing address and shipping address? I do not recall having my billing address to that stranger's house. My billing address will most likely be my primary residence. Some people can even have their primary residence different from their billing address if they move a lot or just relocated in a new place. If I use my billing address, it will send it to me, not to the stranger's house. If I use my shipping address, the transaction will not go through as the address does not matches with my billing address. Ergo, the best implementation is to create a filtered address entry record with:
- Table CustomerAddress:
- CUSTOMER_ID As Foreign Key
- CUSTOMERADDRESS_ID As Auto Increment Identity Primary Key
- FIRSTNAME
- LASTNAME
- ADDRESS1
- ADDRESS2
- CITY
- STATE_ID Foreign Key
- ZIP
- COUNTRY_ID Foreign Key
- REGION_ID Foreign Key
- Table STATELIST:
- STATE_ID As Auto Increment Identity Primary Key
- STATE_VALUE
- Table REGIONLIST:
- REGION_ID As Auto Increment Identity Primary Key
- REGION_VALUE
- Table COUNTRYLIST:
- COUNTRY_ID As Auto Increment Identity Primary Key
- COUNTRY_VALUE
The original Customers table can also have additional columns, such as foreign keys "Preferred Shipping Address" (CustomerAddress.CUSTOMERADDRESS_ID), "Preferred Payment Address" (CustomerPayment.PAYMENT_ID) where the website service can use it.
An alternative way is the Customers table to have shipping_address1 (shipping info) and billing_address1 (billing info) columns. This will solve the most critical issue, but will still limit the user to have only one shipping and one billing address.
CUSTOMER ADDRESS INFO Critical Importance: 9
- PAYMENT INFO (CREDITCARDTYPE,CREDITCARD,CREDITCARDEXPIRATION): User should be able to enter more than one credit card. Ergo, normalizing with the following is required. Otherwise, user cannot pay an order with multiple formats of payments. This is not popular and a big need from the user's need at this moment. If this is not implemented, user will have to change the payment info every time he wants to use a different payment.:
- Table CustomerPayment
- PAYMENT_ID As Auto Increment Primary Key
- CREDITCARD_ID As Foreign Key
- CUSTOMERADDRESS_ID As Foreign Key
- Table CustomerCreditCard:
- CREDITCARD_ID As Auto Increment Primary Key
- CREDITCARD_NUMBER
- CREDITCARDTYPE_ID As Foreign Key
- CREDITCARDEXPIRATION DATETIME (DATETIME is more convenient)
- Table CreditCardType:
- CREDITCARDTYPE_ID As Auto Increment Primary Key
- CREDITCARDTYPE_NAME
CUSTOMER PAYMENT INFO Critical Importance: 5
- PHONE INFO (PHONE): If customer can or will in the future enter more than one phone number, such as home phone, work phone, mobile phone, even fax phone, then (and I think it is ideal for a DVD store. Let us say you forgot to deliver back your dvd lease or the DVD store wants to send you marketing sms text messages?):
- Table CustomerPhone:
- CUSTOMERPHONE_ID As Auto Increment Primary Key
- CUSTOMER_ID As Foreign Key
- PHONE_NAME
- PHONETYPE_ID As Foreign Key
- Table PhoneType:
- PHONETYPE_ID As Auto Increment Primary Key
- PHONETYPE_NAME (=HOME, WORK, MOBILE, FAX, ETC.)
CUSTOMER PHONE INFO Critical Importance: 3 - EMAIL INFO (EMAIL): If alternative email is used for recovery of password in the present/future, then:
- Table CustomerEmail
- CUSTOMEREMAIL_ID As Auto Increment Primary Key
- CUSTOMER_ID As Foreign Key
- EMAIL_VALUE
- EMAILTYPE_ID As Foreign Key
- Table EmailType
- EMAILTYPE_ID As Auto Increment Primary Key
- EMAILTYPE_VALUE
CUSTOMER EMAIL INFO Critical Importance: 2
ORDERS TABLE, ORDERLINES, CUST_HIST
Unlike creating an account which involves the involvement of inputting stuff in customers table (which we split it up into 12 tables in the end), creating an order involves the affection of many tables (this is all speculation because I have not tried the web version of DELL DVD Store). Here is a summary of my speculation how the flow of actions incorporate to the affection of database tables.
So when we purchase dvds, we input new records in the orders, orderlines, cust_hist table. In addition, we update a specific product in the inventory table for every order line.
For the Orders table, one of the annoying thing is there is no receipt #. If we want to print an invoice for the customer, do you expect we are going to use the ORDER_ID? If we need to delete this order and need to recreate it, is it good to use ORDER_ID? It should be a static value. When the customer asks us for a refund 60 days later and gives us the ORDER_ID which we deleted and recreated with a new record, do you think we will find it?
ORDERS TABLE Critical Importance: 5
For the orderlines table, there is one thing that bothers me a lot. There is no unit price. A second thing is that the orderdate repeats again (its already on the orders table). Unless, there is a distinct difference between those two due to my lack of business logic understanding, I think it should not be there. Again, I have to re iterate the issue with the orderline having no unit price. We cannot rely on the product table field "PRICE" (general info). What if the price of the dvd has a price cut after 2 years? What if the price of dvd has a sale on some certain periods of the year? If we want to print a receipt with the price details of each dvd, we cannot do it as the products table information is not reliable.
ORDERLINES TABLE Critical Importance: 8
For the cust_hist table, I am trying to make some conclusions with it. It looks like you cannot put the same product more than once like many websites do not let you (instead, if you add it again, it will just increase the already in cart item order quantity by 1). This most likely used for the user to see the history of orders more faster by using this table as the start of the query. I just would not have used the prod_id to go to the product to get the product price. There are many ways this table can be used beneficially. I cannot say much though, as it is not a "main core table".
CUST_HIST TABLE Critical Importance: 0
In summary, these are the changes in effect:
We have 4 tables left (Products, Category, Inventory, reorder) to talk about.
The products table is okay. I cannot find anything wrong about it. GENERAL INFO represents Price, Title, Actor. The same feelings I have for the category table.
INVENTORY AND REORDER TABLE
User purchased an order? Update inventory and sales. User refunded an order or cancelled? Update inventory and sales. Received supplier products? Update inventory. Do a physical inventory check up? Update Inventory. For that reason, an inventory_history table is required. The inventory history will insert records as "events". For instance, based on my speculations how the database works, it will have the following events:
- CustomerCreatesOrder (-) ON QUAN_IN_STOCK, (+) ON SALES
- CustomerCancelsOrder (+) ON QUAN_IN_STOCK, (-) ON SALES
- ReceivedSupplierPackage (+) ON QUAN_IN_STOCK
- PhysicalInventoryCheck (+ or -) ON QUAN_IN_STOCK
- Table Inventory_History
ALTER TABLE ORDERLINES
ADD CONSTRAINT FK_ORDERID FOREIGN KEY (ORDERID)
REFERENCES ORDERS (ORDERID)
ON DELETE CASCADE
;
What does that mean? If an order gets deleted, the order lines gets deleted? Can we count the sales or inventory of purchased or cancelled orders on that case? No, so if the company wants to keep track of such statistics and database administration does not want to remove such constraints, then the best solution is to create an Inventory_History table.
INVENTORY TABLE Critical Importance:7
For the reorder table, I think it is okay as it is. A nice thing is if it had a state of the reorder. There is no event that deletes the reorder once we receive the item. To avoid duplicate records, we should add a column that takes 4 states:
New field column for reorder: reorder_state with the following values
- ALERT: The internal system alarm (trigger) detects a specific product is bellow the stock level. Inserts a record with the following fields: PROD_ID, DATE_LOW, QUAN_LOW
- REORDERED: Store reorders any alerts. This will update the existing record the following fields: DATE_REORDERED, QUAN_REORDERED
- PENDING: Supplier receives reorder and starts shipping it. The supplier will update the following field: DATE_EXPECTED
- RECEIVED: Store receives the order. Store does not need to update DATE_EXPECTED field because Inventory_History table will store a record with event "ReceivedSupplierPackage".
If we look a new diagram of the reorder and inventory table, we get the following:
Summary of Enhancements Sorted by Level Of Priority
- CUSTOMER ADDRESS INFO Critical Importance: 9
- ORDERLINES TABLE Critical Importance: 8
- INVENTORY TABLE Critical Importance:7
- REORDER TABLE Critical Importance: 6
- ORDERS TABLE Critical Importance: 5
- CUSTOMER PAYMENT INFO Critical Importance: 5
- CUSTOMER PHONE INFO Critical Importance: 3
- CUSTOMER EMAIL INFO Critical Importance: 2
- CUST_HIST TABLE Critical Importance: 0
Note: Within the analysis, it was determined no analysis was required for Product and Category Table