Building eCommerce is one of the most famous web application that we build as it got so famous, well known, and the need of it is became more than ...
For further actions, you may consider blocking this person and/or reporting abuse
Why not just have one categories table, set parent to null.
Thank you for your suggestion! Having a single categories table with a parent set to null is indeed a valid approach in certain scenarios. This depends on the specific requirements of your application.
However, in some cases, a separate table for parent and child categories can provide additional benefits like allowing easy management of attributes of every category type and simplifies certain types of queries.
Interesting. Could you expound on this? Would like to see benefits...
Yes! In certain instances, I chosen to simplify the
categoriestable by storing only theidandnameof the category. Meanwhile, thesub_categoriestable includes additional details such asid,name,parent_id,description,icon, and aflagto signal anything new or updated in the UI. Additionally, I introduced agroup_idfor cases where subcategories are logically grouped under a common title.In other scenarios, I have followed your recommended approach, especially when a client requested categories and subcategories with unlimited depth. Your approach proved to be the most suitable for handling such cases of dynamic hierarchy.
Nice post, Ezzdin! The database design is really good, and I really enjoyed reading this.
Here are my suggestions -
In the context of
cart_itemandorder_item, you probably don't needproduct_idas it is redundant. The required data can be fetched usingproduct_skus_id.Storing the total directly in the cart table introduces redundancy, as the total is calculated based on the individual product prices or quantities. This redundancy can lead to data integrity issues. Unless you are using some event-driven mechanism such as database triggers, instead of storing the total directly in the database, you might need to calculate the total dynamically when needed.
Thank you so much for your positive feedback and insightful suggestions! I'm thrilled to hear that you found the article interesting.
You have a good point about the
product_idredundancy, and I will updated the structure with this optimization.Regarding the storage of the total directly in the cart table, I appreciate your concern about redundancy and potential data integrity issues and instead of that we can calculate the total dynamically when needed, and actually I don’t know much about the event-driven mechanism and will lock into that. Thanks Adhiraj!
I’m glad that you find the dbdiagram.io tool helpful to you and appreciate your suggestion about the
payment_detailstable, it’s nice suggestion I will keep in mind next time 😀FOREIGN KEY (
size_attribute_id) REFERENCESproduct_attributes(id)ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (
color_attribute_id) REFERENCESproduct_attributes(id)ON DELETE CASCADE ON UPDATE CASCADE
In this case how each column size_attribute_id and color_attribute-id in products_skus knows which enum type is referring to?
EDIT
With this method i see all enum values in list. Imagine many attributes a product can have and each attribute could have sub attributes. Is there a way an id could direct into enum value specifically
Thank you for this article. I'm still learning backend stuff. I wonder if the enum
product_attribute_typeis extensible by a db query when user wants to add another attribute type for example?Could you publish the DDL?
You can export it from dbdiagram.io tool, as I have shared a link to the database design from that tool where you can find an export button to export it. That Database Design
What if you have various attributes other than size and color?