Over on Discord this week we were asked if the below diagram was “good enough” for a retail application.
The requirements were
I broke down the above ERD into chunks to try and explain some of the misunderstandings that had been wrote into it.
There was a clear misunderstanding of foreign keys, the user had made relationships with tables based on the wrong logic, thinking because a user could buy a product that the relationship should be between the user and the product which is incorrect.
Having a foreign key on one table creates a relationship between it and the source, this basically tells you that values you add to the target table MUST exist in the source table unless the column is set to allow NULL’s then it isn’t required for a value to be passed at all.
This is demonstrated in the below diagram
You can see CustomerID 234 in the Customer table relates to CustomerID 234 in the Orders table, if we try to enter 234 into the Orders table and that wasn’t already in the Customer table the constraint would prevent the insert because of the (foreign key) constraint.
There were a number of columns in the diagram that shouldn’t have been in the places they had been put, it cause a lot of confusion especially when trying to create the relationships, most of the primary key columns had been called id, which in itself isn’t a problem but if everything is called id it becomes a bit messy, I gave all the columns proper names and moved the columns around to places that better fitted their function.
This is demonstrated in the My Diagram section of this post.
As some of the items in the database design require a many to many relationship to properly function we need to have a ‘junction’ table to ensure that relationship could be created.
The main relationship being Baskets & Products.
Baskets can have many products and products can have many baskets, to solve this we would need a many to many relationship which was not included in the original diagram.
I re-worked the database design into the below, this is very basic and was supposed to just show how the issues in the original model could be fixed. There are of course other improvements that could be made, however they would be above the scope of this exercise.
In the fictional web store application, a user would have an account page, in there would be a button to upgrade their account, pressing that and successfully making it through the payment process would render their account upgraded, the isPremium column would be set to 1 and the premiumStart date populated with the date and time that the user upgraded their account, this could be the payment success date and time.
There is also a premiumEnd date, depending how the shop is configured, members could have a 12 month subscription so a date 12 months into the future could be added into this field and a worker process would run periodically to check for expired users and set the isPremium field back to 0.
This table was dropped from the model, I didn’t think it had any value, the original ask in the database design had no real requirement for it other than to check if the user was premium.
The reason I changed the cost from FLOAT to DECIMAL is because money requires precision and FLOAT will round your values.
This is a new table which was not in the original model.
The basket state would be used by the shop front end to decide if the basket was in a pending or completed state, depending how the shop was built for the interaction with the user would determine these values, when a basket is checked out and payment is successfully taken the basketState would however be set to 2.
This is another new table which was not in the original model.
Each basket can have many products and products can have many baskets, this is a link table to create that many to many relationship.
The final code for this model can be downloaded from GitHub along with the diagram.
If you would like to play around with the design, I have also created a dbfiddle which you can find below.