eCommerce Database Design: Diagram & Explanation
Vložit
- čas přidán 9. 07. 2024
- 📝 Bonus PDF: This ERD, descriptions of each table and column with sample data, and SQL scripts to create all of the tables on your own database: www.databasestar.com/dbdesign...
🎓 Learn how to design an effective database and create it using SQL: databasestar.mykajabi.com/edd...
An eCommerce database design is a good example of a real application that you can design a database for. It has some simple and common concepts such as users, and some complicated features such as products, attributes, and orders.
In this video, you will:
- See my database design for a fictional eCommerce database
- Learn what requirements will be included in the design
- See an explanation of each table, why it was designed this way, and what each column is used for
- Understand how tables are related, and why, to support the features of an eCommerce database
This is just one example of an eCommerce database design, and it’s how I would design one. You may have other ways of designing it - feel free to share in the comments below!
There are no SQL scripts to create this database, but if you want to create it, you can learn how to create an SQL script from a database design in this video: • How to Create SQL Code...
Timestamps:
00:00 Our requirements
01:23 Full design
01:46 Site user and address
03:53 Payment method
05:01 Products
11:02 Shopping cart
11:46 Order
13:55 User reviews
14:33 Promotions
Links:
ERD diagram shown in this video (a PNG file): dbshostedfiles.s3.us-west-2.a... - Věda a technologie
Bonus PDF: I've created a bonus PDF for this database design, which includes the final ERD, a description of each table and column with examples, and the SQL scripts to create all of the tables. Get your copy here: www.databasestar.com/dbdesign/
Thank you very much😍
Page not found... Can you fix it please? :(
Which app/website u used for designing
Thank you very much.
Great work! I just want to create my first ever full stack project and was thinking how to design database. Thank you for this video.
Glad to hear you enjoyed it!
Thanks for the explanation... This is something I'll keep busy at studying
Glad it was helpful!
Your ability to explain is actaully crazy good! Thanks for all this material.
Thanks, I'm glad you like the videos.
Awesome, I just found this channel and its pretty helpful, thank you! just subscribed
Awesome, thank you!
Your explanations of all concepts are excellent. Thank you 💝💝
You're most welcome!
Awesome explanation in 16 min video, I would like to thank you teacher.
Glad it was helpful!
This is so helpful ..Many Many thanks to you!! ..God Bless U
Glad it was helpful!
this is great, thank you
Thanks!
Thank you, it's very helpful
Glad it was helpful!
This is awesome explanation and really rare diagram picture). i came here after one was explaining to save variation_options in a string like yellow-XL and make it unique by sorting alphabeticly.
Thanks, glad you like it!
Loved it. Thankyou
Glad it was helpful!
I loved, Thank you
Glad you enjoyed it!
Very helpful. Well Done!
Thanks!
This is great! Thank you!
just wanna add something about the user_payment_method: You don't store credit card info if you're not PCI DSS compliant.
No problem!
Good point about credit card information and being PCI DSS compliant.
Excellent Video!
Thanks!
Thankyou for this wonderful explanation
Lot of my doubts automatically got solved as I kept listening your explanation
Glad to hear it!
Thank you so much!
You're welcome!
Thanks for the video! A few comments:
* I don't think promotions should apply to categories, because sometimes you just want to have a sale for a product or a product_item, not to whole categories.
* The shipping_address on the order shouldn't be a reference to the address, in case the address changes, the order should have a denormalized version of it.
Thanks David! Two very good comments.
Yes, you may not want to have a sale for a whole product category. So it's probably better to have a sale related to a product instead, as you suggested.
Good point about the address changing. You could use the existing design to cater for addresses for an order where they shouldn't change, but yes having the address on the order is probably better in this situation.
So how will the denormalized version be.. Please Enlighten me as a beginner
@@salive8754 denormalization means bundling data together in a table instead of spreading it across separate tables (bit of an oversimplification). In this case it would mean having the fields required for describing an address in the order table itself, so each order has a copy of the address originally used for placing it, independent of changes done to the actual address record.
Dear Salive8754, I understand your explanation; what seem elusive to me yet is how this scenario would happen and how often can one expect to happen in a daily system usage? Once an order has been requested, all its related data (e.g., address) is atomically applied in any report generation. This report then should be immutable, as far as I can understand. Sincerely
Great explanation.
Thanks!
Awesome!
Thanks!
thats all I need Thank you
You're welcome!
@@DatabaseStar so bro, how can I query all information relates to the product? By using Node JS
like MongoDB
I've been stuck putting the product part of this database structure to use and I've watched this video atleast 3 times and I get the logic. The issue is how does one implement this in practice...
I'll list my steps on what I thought was the right way:
1. create a category and have an option to link it to the parent_category. This way we create a hierarchy in this table.
2. Create a variation ('color') and asign them to the category. ---------------> This is where my first issue is - shouldn't we be able to add color to multiple categories that use them? Like t-shirts, pants, smartphones, etc... That would mean that we need a many to many relationship as multiple categories can have multiple of the attributes and vice versa?
3. We create a product and the user should fill in all required fields so (product name, description and image) ----------------------------------> here is the second issue: do we store this in the database and then have an option make variations of this product? But then that means if we don't have a variation added that it would be an empty product listing on the listing page. So my second solution was why not make the (create product page) a whole page of all the fields both from the product table and the product_item table?
4. If we would go with the approach of step 2 and we use many to many that would mean I can query for all the attributes linked to the category that this product has selected and show the options on the page? for example CLOTHING has been selected then we should return color, sizes but if we have SMARTPHONE selected then we return color, storage_capacity as options.
5. Does the user have to select atleast one of these attributes? And what if they select multiple options that would mean I need to make a new product_item for each selected option? -----------> another issue pops up what if the color dictates which sizes are available? for example a red T-shirt has Small, Medium sizes and a blue T-shirt has Small, Medium and large sizes. Maybe I'm way overthinking this but it's hard to come up with a way that satisfies all these needs...
On top of all of this I'm using Laravel (PHP) and relationships to "simplify them" but it feels like it makes it even less "readible"...
Sorry for rambling on but I've been stuck on this for days drawing on paper and writing down logic but whenever I think I got it and start putting it into practice new issues popup...
I appreciate anyone reading this and feedback or your ideas would be greatly appreciated!
Thanks for watching and for the in-depth comment!
Here's my response:
2 - Yes, that would be a better way to do it I think, as you would avoid duplicate rows that represent the same thing (e.g. "color")
3 - Yes, that would be an issue. For a Create Product page, having all of the fields would be a good way to do it.
4 - Yes, you can write a query that does that, so you only see the relevant options.
5 - Yes, this design assumes there would be one product_item for each different combination you need. Which would also mean, if you don't have some combinations (e.g. there is no Large Red t-shirt) then you don't create a product_item for that.
Of course, feel free to adjust anything from this design to meet your needs!
This is a great example to help me build my first relational data tables in Microsoft Dataverse. One question, why do you create a separate table for order_status? What's the advantage compared to just creating a choice attribute in the shop_order table?
Thanks! The reason for a separate order_status is that there is a specific list of order status values, such as "Ordered", "Delivered", "Cancelled". We can add those values into the order status table, and when we select an order status for an order, we select a record from that table. This improves the quality of the data.
If it's just an attribute in the shop order table, then it would be a free text field, leaving it open to put any kind of value in there.
Thanks for all this material
No problem!
hi brother wonderful work, i am just getting started wit mysql
Good to hear!
Fantastic video, Ben. This is my first time using a "pivot" table for product variants and has cleared a few things up for me. Would you have any resources on how data retrieval is carried out with the product variants?
Thanks! The only thing is my more recent eCommerce Product Design video which includes some SQL on how to do this, which may help with data retrieval: czcams.com/video/8bkGKwb29L4/video.html
Otherwise I don't really have anything.
Great!
Thanks!
very nice explanation sir
Thanks!
you're the best
Thanks!
I really appreciate the time you took to make this video. It's beautifully explained and has a great amount of information! If I could, I would like to ask something in regards to the shopping cart and orders.
Say a user is signed in and he adds a few products to the shopping cart - now those products will be saved to the database and added to the shopping_cart as I understand. Then when the user decides to checkout those products, will they be copied to the shop_order table through order_line? Or is it that when the user adds products to the shopping_cart, those products should get added to order_line directly too?
Thanks! I'm glad you like the video.
Good question. I think it's the first scenario: they will be copied to the shop order table. However, another comment suggested that they are kind of the same thing so the shopping cart table could be removed and perhaps an order is just created. Hope that helps.
Amazing explanation, Thanks a lot
You are welcome!
@@DatabaseStar 9:05. You've mentioned that product_item table keeps record of e.g the product slim t-shirt with size M and color of black, but those credentials are stored in product_configuration table, which has many to one(respectively) relationship with product_item table, that means one product item with a specific SKU and quantity can have multiple color, size etc. I'm having s little confusion at this part of design, would you help me understand please
Excellent work sir
Thanks!
Hi, this is great video. Thank you for sharing your knowledge.
I was wondering how would be the approach for subcategories in this design, considering it may have many subcategories for many categories. Ex. 01 dematting Brush / (in dogs, in cats, in rabbits as subcategory) / (in pets or in accessories as category)
Thanks! I would suggest using the product_category for subcategories. The table is related to itself, so a category of "dematting brush" could have a parent category of "dogs", and that would have a parent categry or "pets" for example.
Great video, Relational DB can be tricky has been working on a project for about a month. Each design i come up with doesn't seem to fit. How long does it take to design a complex DB diagram
Thanks! It can take anywhere from a few hours to a few weeks I think, depending on how well you know the data and requirements and how complex it is. It's something that can evolve as you start using it, I think,
~@13.20 I actually think it's better to not have an extra table for the cart. Just use order as you mentioned and fill it up with values. The cart is in my opinion just an entity that exists in the runtime. The shop then functions almost as a pipeline for filling out the order details.
Good point, I think that could be better too.
@@DatabaseStar Hehe, we'll see if it holds up in the long run, but so far it seems to work ;)
At 11:49, you have created a shopping cart table which only holds its own ID and the related user's ID. Each cart item then stores the cart ID. Would it be a bad practice if, instead of creating a separate table named shopping_cart, each cart_item directly referenced the user ID? Because each user is going to have only one shopping cart anyways?
That's a good point and I think it would be a better way of doing it, actually. A user can only have one shopping cart, so your idea can work.
@@DatabaseStar Thanks! Your videos help me a lot!
Really good video
Thanks!
Nicely done, but how can we assure having one unique variation per product item? Let's say we have color (red, blue) and size (S, M) variations. From what I see in the video nothing is stopping us from associating Red-S and Red-M with a single product item. What I can think of is adding the variation_id also into the pivot and create a Unique key with both the product_item and variation_id. What would you do to handle this situation? Thanks!
That's true, there's nothing stopping this happening in the current design. Adding a unique key/unique constraint would enforce this, as you suggested. So if someone adds a second row with the same combination, then they would get an error.
Thanks for the video it has been of great help. I'd like to know whether the entities here are the exact tables to be created in the database. if not all of them, how can i identify the required ones
You're welcome! Yes, these would be the exact tables I would create. In this design I suggest creating all of these tables to achieve the features that are described in the video. But this is just one way of doing it - there are many comments on the video suggesting some different ways, and even better ways than my design
I've been really enjoying watching your videos, even though most of the videos I watched were uploaded over a year ago. It's super well explained and therefore easy to grasp.
Quick question regarding the product_item table:
It feels like it would make more sense to add "variation_option_id" as an attribute to the product_item table, rather than having both of them be connected in a relation table, especially cause the shopping_cart_item is linking to the product_item and you would need to know which variation you're ordering in your shopping cart when you access it. Maybe I'm thinking about it totally wrong though 😅
Thanks for the feedback, glad you like the videos!
Yes, that could work as well, but I think it may not allow for a product to have multiple variation options, such as Colour Red and Size M.
There have been a lot of comments with suggestions and questions on this video, so I think I should make a follow-up video with this product concept with more examples.
@@DatabaseStar Thanks for the clarification, I understand what you mean.
I would certainly love to watch another video on this concept. I mean there really is no limit to what you could add to a database and I really like how you often point out limitations of your designs and also mention ways to improve on it
Thank you so much for this video! I have a question though,at 13:08 you mention that this design assumes that the application would convert the shopping cart to shop order and you mention another design which could work with only shop_order table, so how would you go about implementing that design?, which tables would you connect?
Glad you like it!
Yeah that design could work by connecting the tables that are currently connected to shopping cart to connect to shop order instead. You may want to have some kind of status on the shop_order table to indicate the order hasn't been placed.
Hello, thank you for this video. It helps me a lot. However, I've noticed that the entity "shop_order" and "site_user" entities have no connection.
Glad you like the video! What do you mean that they have no connection? I've checked the ERD (linked in the description) and there is connections or relationships to other tables.
Love this! Do you plan to have a recurring payment system database like how notion or spotify or any subscription service has? Liked and Subbed!
Thanks! I can create a database design for that in the future.
I think our design could become simpler (having less tables and relations) by using Enums too, for example instead of having a order_status as a separate table, and a foreign key order_status in shop_order table we would have had the order_status as an enumerated type. Same for payement_type table.
Good point. We could have enums for those selections. However, enums are not supported by all databases. Enums are also not easy to update if you want to add or remove or rename values that are applicable (using a lookup table is much easier). Also if you want to add more context or description to each item, you can't do that with an enum.
how is integrity maintained between product category for variation/option and product category for product/item? It seems I can compose together a product configuration for a product item that is in a different product category to the variation option chosen
That's a good point. The design does let you do that at the moment which is not ideal. Perhaps in the application there could be a way to filter the list of available variations for a product when it is added. Or perhaps the data being added to the table could be done via a stored procedure which could include a check for this. Or maybe there's a way to add a table that can capture the valid combinations somehow.
Awesome ERD. Thanks a lot sharing.
I just had a question about maintaining history. Suppose it’s been two months since our eCommerce store is running smoothly and a user A has ordered 6 times and is a happy customer. Now he does change his address I mean edits his address but we have previously delivered orders on this address so when user then goes through its history, would not it see changed address instead of one where address was actually placed?
Thanks!
Good question. That can be covered by a user adding a new address for their future orders. Old orders would be at their original address, and any new orders are sent to their new address. We store both because of the user_address joining table.
why the relationship between USER and CART is one-to-many, I just think that it's one-to-one.
That’s a good point. It should be one to one, because a user would only have one cart at a time.
Umm i think 1 user can have more than 1 cart exp.shoppee or amazon
Not necessarily… the application might allow a user to have multiple carts … equaling multiple quotes, one for each cart … the user can then decide which cart to “finalize” before payment … at which time said final cart on payment is then one-one.
Hi, great video. I have a question, lets say I create V variation for C category and VO variation option for V variation. What stops me from adding VO variation option to a product entry belonging to some other category than C?
Good question, I don't think there is anything stopping you doing that, which is a drawback of this design.
Great video! I do have a question about your shopping_cart relation to user_id. Shouldnt it be a one to one relationship, since each user will have one shopping cart that belongs to them, and the user will belong to one shopping cart per account? Thank you!
Thanks! Good question. Yes, you're right, it should be one to one because there could only be one shopping cart per user.
@@DatabaseStar Thank you for the quick response. Another question I have is about the self join for the product_category table, is there any docs on where I can learn how to implement the self join relationship if I were using Flask/SQLAlchemy for my backed? Its an incredibly interesting topic being able to stack the categories that way to avoid creating unnecessary tables!
Are there standards about having certain things in a single database? For instance is it acceptable to have payroll and other accounting info within the same database that contains e-commerce & user data tables?(with encryption of course)
Good question. There may be some standards but I'm not aware of any. Some things to consider are which applications are using the database (e.g. if there is one payroll system then maybe that has its own database), which teams are working on the applications, how often they may change.
for the product variation: there is 'name' and 'value'. which makes sense. but. how to know the data type of the 'value'? because it can be a number, text, enum, etc
Good question. Allowing this kind of flexibility with product variations (which is like an "entity attribute value" design) has adrawback of enforcing data types. You could have specific columns for attributes but the attributes would need to be added to the table as needed.
How can I determine which variants the customer selected when they place an order for Product Item 1? For instance, if a customer orders a T-shirt with a blue color and size medium, how can I retrieve the specific variants they chose?
You can use a select query that joins to the related records in other tables.
great content, Im interested to see how search products would work in an ecommerce website i.e. given a keyword how would I retrieve the related products in the least time from datablase. Also a video on how to index and what to index in these tables for best performance would be helpful. I don't see a whole lot of content on youtube on how to implement search for an ecommerce website.
Good question! For searching, I think you may have a SELECT query that would have a series of OR statements. For example, searching for "green":
WHERE product_name LIKE '%green%'
OR product_description LIKE '%green%'
OR attribute_option_name LIKE '%green%'...
There may be better ways to do this. One issue that comes to mind with this is that performing searches like this (wildcard searches) can be quite slow. There may be database features that you can use for searching but I'm not that familiar with them.
I can look into it and create a video for it.
@@DatabaseStar appreciate your response yes if there is a faster way to achieve this a video will be great help
Great work brother, in case i want to a multi vendor ecommerce what other tables to i require n relationships to this existing one?
I assume a vendor = store or website? For example, one website/vendor to sell books, another to sell mens clothes? If so, you could have a vendor table linked to maybe the product table so you can link products to the vendor you want to allocate them to.
thank you for the copy of diagram, brother.
No problem!
How can I make sure there is no duplicate addresses ? As and when I enter a address it will generate a new key for that address and add it to addresses table with new key and add the same address key to user_address relation.
Good question. You can avoid duplicate addresses by identifying which columns define what a duplicate is (e.g. address 1, address 2, city, country), and then creating a Unique Constraint on those columns. When a row is inserted into the table, but it has the same combination of these values as an existing row, then you'll get an error such as "unique constraint violated".
Thank you sir.
I was wondering how to implement the payment tables if I am using an external payment gateway 🤔
How to implement them in the application or the database design? In the database design you could follow a similar process and add the tables you needed.
How to handle product without variation and product with variations in this case??
I will have diffrent names if variations are there and stock, sku, etc need to be on both table product and product_item?
Yes, if you want different names you could have different product items.
Hi sir, good work, please may know the software you use for creating the ERD ? thanks
Thanks! I'm using a tool called LucidChart
Hello. First of all, I really appreciate the video; it really is informative and concise. However, I would like to ask a question. If my service only has size as variation, and I decide to use the method from your video about Product Attributes (Product Table, Size Table, and Product Entry Table). How would I go about the cart-item and order-line step? Should I add both Product ID and Size ID as foreign keys for the Cart Item Table? Or should I add a primary key to Product Entry table and add it to the Cart Item Table? I am looking forward to the answer. Thank you.
Thanks! I think this design will work the same regardless of the number of variations you have. You could stick with the approach in this video (adding a product_item to the shopping_cart_item table), or you could add the size_id to the cart table. I think either could work.
Great work Thankyou , i have a question please, relationship between shop_order and user_payment_Method . so why (many )relation is in the side user_Payment_Method not in side of shop_order? Is relationship reversed? thank you ...
Good point! I believe the arrow is facing the wrong way on the diagram. The three-line part should be on the FK shop_order.payment_method_id, and the single line with the line crossing it should be on the user_payment_method.id.
9:05. You've mentioned that product_item table keeps record of e.g the product slim t-shirt with size M and color of black, but those credentials are stored in product_configuration table, which has many to one(respectively) relationship with product_item table, that means one product item with a specific SKU and quantity can have multiple color, size etc. I'm having s little confusion at this part of design, hope someone can help
Thanks for the comment and example. The product_configuration should have one value for each variation and product. So, product_item 1 has a SKU of 2001, which has two rows in the product_configuration table: one for variation_option 4 and one for variation_option 6. Let's say that variation_option 4 refers to a color of Black, and 6 refers to a size of M. That way you can find all of the attributes of the product.
@@DatabaseStar How to know how many products of color black and size M are there?
Hello sir. Is the relationship between user_payment_method and shop_order really ok? I just can't understand how user payment method table has a primary key which somehow is on the many side of the relationship
Good point! The PK-FK is correct, but the line is incorrect (it should be around the other way): there should be one user_payment_method for each shop_order.
Really informative. Thanks. i cant make up how SQL queries will be to select products and its variance ???
Thanks! You would select the columns you need from the product and variations tables, and join between them. You can filter on values in any of the columns, for example if you only want to see variations for one product.
By the way what tool has been used here to draw ER diagram?
I. E Which online tool is the one used in above video?
@databaseStar
I use LucidChart to design these diagrams.
Question: About the product_configuration table, does this not store a ton of redundant information? Let's say you sell clothing and thus you have a certain product let's just say a T-shirt, but this T-shirt has variations in many colors and sizes. You also store the material, now you would have assign the material toe very product variaton for (=every available color times every available size).
Yes, you may have a lot of data in this table, but it would be valid because it matches to real products. Depending on the size of the store, you may have a few hundred or a thousand rows in the table which I think is OK.
@@DatabaseStar Ok this is clear. For my personal alteration I'm thinking about adding a product_type table to store all available (thus available for at least one product) combinations of variation_options, so I would not have to repeat that data and potentially make queries easier. I know this is very case-based but would you recommend this and in/for which cases?
Dear DatabaseStar, thank you for the vídeo. Amazing work; truly well explained. During your 9th minute and 50 seconds, I got wondering why you chose to define the cost of the product in the product_item table, instead of the of the product_configuration one. As you well pointed out previous in the video, a product's cost may well vary depending on the options selected. Extending the problem, an Item's image may also vary depending on it's configuration (e.g. size, color, fabric, etc.). Therefore, one could well assume that the property Item_image should also be placed in the product_configuration table. Please let me know your thoughts on this regard. I thank you for your time and I hope to hear from you soon. Sincerely,
Hi, thanks for the question! The reason the price is in the product_item table is because the record in this table represents a unique item that is sold: it has a SKU, a quantity in stock, and a price. A web page might show the data from the product table, and if a user selects different colours or sizes, then different product_items are shown and then different prices and SKUs.
The product_configuration contains many records for each product_item. It's a list of all of the options that apply to the product.
Regarding the image: the design I have isn't that good. It should be in a separate table as product_items could have multiple images.
Hope that makes sense!
Thanks for your video.
I have a question: what if we want to have multiple images for each product ?
Should we create a new table "product_images" with three rows 'id', 'url' (as String) and 'product_id' (as foreign key), and remove the 'image' row in product table? Or is it possible to store them directly in the product table (and if yes, how ? Since I think it's not possible to have array as data type in mySQL) ?
Good question! Yes that's exactly what you can do (a separate table), and I think it's better than trying to add it to the product table.
What if we have diffrent images for each variations? And how to manage product without variation?
There is a way to store multiple images within one product_img column but it requires some clever string formatting for example:
In php u can store all the images names that are selected from the admin and concatinate them with a ',' by using a foreach loop on each image uploaded like this 'products/ ' . $img-name . '.' . 'originalextension' . ','
That will add everything into a string like this: "products/img-name.ext,products/img-name2.ext,products/img-name3.ext,etc...
You can then when you want to display them separate the string using the explode method and use a foreach to display each of them on the product page. You can easily search for this on the internet but the option is there :)
thanks for great vedio, can we put the variation option table as an attribute to the variation table?
Yes, you could, but there might be duplicated data in the table.
Does anyone know how to do filtering of product items by their variation and variation options? I'm really stuck with this problem right know, Java 17 + Spring Data JPA, but still can't find a valid way to perform adequate filtering query. I will be very grateful for advice
Could you join to these tables and use a where clause? Unless I am missing something...
up
I really like your video, and honestly i was looking specially for products categories i saw in your ERD, and i have a question why we don't create a separate table. One for Main_Category lets say (fresh foods) and another table for Sub-category like (dairy) and third one like Subsub-category (milk). Is that make the website slower!
Thanks! Good question about the categories. The reason that we have a single category table instead of 2 or 3 or 4 is because having a single category table is more flexible. You can set up two levels of categories, or 3, or 4, or as many as you want. A category just links to its parent category.
It also makes it easier to change the categories if needed. For example, if you split an Electronics category into TVs and Computers, then it's easier to do this in a single table. If there are multiple tables, you would have to move data between tables and update links from other tables.
thanks for explaining :) @@DatabaseStar
Orderlines would likely need to be able to exist after a product was deleted, meaning that any data you might need about the product, should be stored on the order item. (Assuming you're not implementing some form of softdeletes).
Also not crazy about the way you're storing categories, at first glance referencing parent is fine, but i suspect you'll quickly fins youself fetching all categories just to rebuild your "tree".
Good point about orderlines and deleting records. I would recommend using soft deletes in this instance, so you don't lose any information for related records as you have suggested.
Good point about the categories. It's possible to rebuild the tree of categories using a concept called a "hierarchical query". Each SQL vendor has syntax to get this kind of result.
What do you do for department and category images?
If you want images for those, you could add an image column to those tables and add the image there.
What I really don't understand is how addresses can be shared with multiple users? It prevents data duplication, but what if a user edits his address. Will that effect others users addresses?
Good question. If a user needs to edit their address, they will change their address on a screen, but perhaps in the database creates a link to an existing address record. It shouldn't affect other user's addresses.
how about using UUID ? should its inside user table?
Do you mean as an alternative to the id column? If so then yeah that can work.
can you do one for a SaaS e-commerce schema, with multi tenancy in mind? something like shopify please.
When you say multi tenancy, do you mean multiple customers or accounts with their own dedicated area of the database?
I have a doubt. If am about sell a shirt it can have multiple Variant combinations. Right? There can be different color options as well as different sizes. How can i handle the combination of these with this DB structure?
Yeah that's right. This structure is designed for that, you'll have different variation options and product items to cater for it.
@@DatabaseStar I suppose my question was not straightforward. I'll try to explain with the same example as above. Consider I am selling a shirt. The product details will be persisted in the "Product" table. This product can have a category structure like Clothing -> Topwear -> Men. This can be achieved with the help of the "product_category" table. This shirt can have different variations of colours. So I will add the "COLOUR" entry in the "variation" table and its values like "Green", "Blue", and "White" in the "variation_option" table. New product items for each of these variations will be added in the "Product_item" table and it will be linked to the "variation_option" table with the help of the "product_configuration" table. I understood till this. and everything works.
But, under the blue shirt product item, there can be size variations, right? it can have "S", "M", "L", "XL", "XXL" and other similar things. How can I add these variations to the table? Or is my first part wrong??
Basically, what I need is I want to store a set of shirts in different colours and sizes. How can I properly store this?
Shouldn't shopping_cart_item have a FK to the product_configuration table instead of the product_item table? Since having a FK on the product_item will only add the item without any configuration? Same thing for the order_line FK.
That's a great point. It actually should have a link to the product_configuration table, otherwise you'll get the issue you mentioned!
Two things come to my mind regarding the problem you pitched out and the solution;
Firstly, since the slim t-shirt is black and also in size M, this particular item occupies two rows in the product_configuration table, therefore an array of product_configuration FKs would be needed to capture the entire item's configuration in the shopping_cart_item table. Is that right?
Secondly, can you please further explain the problem you're describing? It does not make sense to me, because if I am not mistaken, to get the item's config, all you have to do is to query the product_configuration table with product_item_id. Therefore any shopping_cart_item and product_config linking seems redundant. Or is there something I am not seeing?
Or maybe I am completely wrong and just can't comprehend your point correctly lol, my apologies in that case.
suppose we change the details of a product like price and name etc. It will reflect in order history too. how will we resolve that?
Good question. You could prevent "editing" of products, and when changes are needed, a new product row is created in the table.
@@DatabaseStar I also had the same doubt, could you elaborate further? Like... creating a new product line in the table, and the current one becoming unavailable for purchase? I thought about the following scenario: the user makes a purchase, and later checks the history, but the prices, names, or any information related to the product changes. How would we maintain the integrity of this history while respecting relational database best practices?
Could you update the ERD png taking into considerations of some of the issues earlier comments' mentioned?
Good idea! I can update the design and add another PNG file with the comments.
@@DatabaseStar Thanks for your effort. I am watching your database design playlist daily. It's great fortune for newbie like me. Your updated ERD will give us an idea about how to make decision when some requirements may change along the way. Please, do it for other ERD png as well that would be great help.
How can i query a product with variation
You could select from the two tables using a join.
where is the line of shop_order -> user_id line ?
Ah you're right, there should be a line there!
I have a question about shoping_car_item and product_item; shouldn't they be related one to one , I mean each product Item can be added to Shopping Cart ITem for the sake of common sense but not more than one diffrent product that's why we keep the quantiity of product inside modal.
Good question. Yes, you're right, it should be one-to-one. It is a PK-FK relationship but there should only be one product_item_id for each shopping_cart_item.
@DatabaseStar can you elaborate your last sentence a little bit more? What do you mean exactly? You are saying, hypothetically, I have a phone for a product item, and it can be added to different shopping cart item.
Hey, I think Price should be present inside product_configuration.
It could go in that table, but there would be multiple product_item entries and therefore multiple prices for the same thing.
how the product will have multiple images?
This design only has one image per product, but you could expand it to use a separate product_image table to allow for multiple images for a single product.
Can this be used for NoSql or Non relational database? Like MongoDB ??
Yes, the same concepts could apply, though I'm not sure about the process to create a database with noSQL.
But i have a question, how can we know, every uniform stock where the size is m? or size is L, how many m and how many L. thaks
You can query the product and related tables for the variation where the size is M
Why not storing the city the same as the country in seperate table ?, bth i didn't understand the pros of doing the country in seperate table , and in the product item when you specefiy configuration for it the table has variation_option_id which refers to the variation_option table which require a variationId which require categoryId so this way the options are only for the category and i cant set an option for one product item
The reason the country is in a separate table is because the countries are a defined list of values. There are about 200 countries in the world, and they don't change that often (they do change but not often). For cities, they may not change that often, but there is a larger number of cities in the world, and how do we know what counts as a city?
@@DatabaseStar Thank you great video, I'm creating a fullstack ecommerce from this video, great job.
What is the name of the software that you are using to create this ER diagram?
I'm using LucidChart.
Why did you create a FK from the PK inside the same table? (product_category)
Good question. This allows us to do something called a "self join", which is where the parent of one record is another record in the same table. It means that a category (e.g. Electronics) can have a subcategory (e.g. TVs) , and both the Electronics category and TVs subcategory are records in the product_category table. It also means we can have many levels of categories, all in the one table, without the need to create multiple tables.
@@DatabaseStar Wowww thanks a lot for your response!
Please what is product configuration
What do you mean?
What program do you use to draw erd?
I use LucidChart
Hi, this is great. Thank you so much for your work.
A question: How would you put a new Supplier entity in the middle of this? Considering that a product can have many different Suppliers and a Supplier can have also many different products?
Would you create a new table in the middle of Supplier and Product, in the middle of Product_Item and Supplier or in a diferrent place?
Thanks again.
Good questions. Yes, you could have a table between supplier and product, as this sounds like a "many-to-many" relationship.
@@DatabaseStar in my current E-commerce design i have the supplier table because i am building a platform that will give a businessman some freedom along with some automation
@@DatabaseStar Thanks. I did that. Now my problem is that I have a supplier table that looks similar to the users table (e.g. it has name, phone number and multiple addresses too). What would you recommend I do?
1. Create another table for suppliers_addresses (and also maintain the Users and users_addresses table)
2. Or create a more abstract table like for example Persons that contain all the possible fields from suppliers and users and also a TYPE field to determine if it´s a user or a supplier?
Thanks again.
@@anarkholico Man, i tried to do something similar. I tried situation where one product can have many sellers, and of course seller can have many products. But that is disaster to manage. it is better if one seller has product same as other seller. Otherwise, how would you manage situation if seller's product has two colors and each has 2 sizes while other seller has just one color different from other seller's two color with different size? Seller_id should be going mentioned in all variation_options coz if it was just for product, then it has a lot of options but available on different sellers partly. So my suggestion is, worldwide famous e-commerce websites dont do that, better you not too. It is only ok if it is price compare website where you actually need not user to choose between variations of products, but just see the product and price for it.
@@DatabaseStar I think the supplier can have many products
while the product can have only on supplier.
you've missed product images. you only designed a column in database for image. a product can have many images. using the above design, we can only have a single image for a product. how will you address this issue?
Good point! Multiple product images would be common with eCommerce websites. I would probably add a new table related to the product table that contains images. This new table would have an ID for the image, a field for the image, and a product ID to link back to the product as a foreign key.
How common is it to create your own e-commerce platform by creating a database design, integrating shopping cart workflows & payment methods, etc versus using woo-commerce, shopify, etc?
Also, do you know if there are platforms that give users read access to database tables?
Good question! I don't think it would be that common at all. If you want to start a business to sell things on an online store, it's usually better to use a proven solution such as Shopify or WooCommerce like you said.
I don't know, actually. I'm not sure how the platforms allow access to the database tables. I assume it would be in their documentation.
sir am requesting if you can make a tutorial on database design for printing service company such as uprinting or flyeralaram
Sure I can create a video on that in the future.
hello database star and subscribers to amazing channel, what is your opinion and best practice for variation design for product in cases? Such as what is your opinion to keep data in json object in table for product? Could you please compare this solutions pros and cons? And your preferred simple solution ideas? Thanks wishing best!
Good question. I've just published a video on this today, which hopefully answers your question.
A JSON model can work as it's more flexible, I think.
i still don't know product_item hasn't name field?
This is because the name is in the product table and the product_item is the item that has the price. We could have a name in the product_item table as well.
Thank you so much for the video! Very informative.
I have a question. How can we effectively monitor a product in stock once an order has been placed? Should we calculate the remaining stock by subtracting the ordered quantity from the total for each transactions without changing the qty_in_stock, or should we update the qty_in_stock value in the product_item table after each transaction? In the latter scenario, what occurs during concurrent operations?
Glad you liked it!
Good question. There are a couple of ways it could be done I think. My initial thoughts are to update the remaining stock after each order (the second of your suggestions). For concurrent operations, you would probably need to contain this process within a database transaction so it completes successfully.
@@DatabaseStar I greatly appreciate your response, as it addresses the question I've had for some time. Moreover, this approach can be used in numerous transactions. Thank you again!