eCommerce Product Database Design: Step-By-Step
Vložit
- čas přidán 27. 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...
In an eCommerce database, one of the most complex parts is how to design for products and product attributes.
In this video, you'll see a real-world eCommerce website or online store, and watch as we design a database from scratch.
You'll also see the SQL that can be used to create the tables, add the data from the website so you see what the data looks like, and how to make changes as our understanding of the website changes.
⏱ TIMESTAMPS:
00:00 - Our website
02:06 - Category of products
03:57 - A single product
10:28 - More product details
19:16 - View another product
21:47 - Nested categories
24:27 - Product filters and attributes - Věda a technologie
This is one of the videos to Design a Database. Appreciate your time to make this video.
Glad it was helpful!
this is the best video I have seen before. thanks a lot !!!!
Wow thanks! I’m glad you like it.
This amazing! Please keep making more!
Thanks, I will make more like this!
Thank you so much. I am building the erd diagram for my own business and this helped me a lot. I appreciate your videos bro
Glad it was helpful!
appreciate this comprehensive video!
Glad it was helpful!
This is so helpful! Thank you!
Glad it was helpful!
this is top content!! thanks a lot!!
Glad you like it!
wow. i just started learning sql. i watched some videos for mysql, mariadb, postgresql etc. with the basics how to create and fill tables with content. but in none of the videos there was any similar way of working with workbench like you did. this was VERY helpful for me and should be a key video for everyone learning sql and db design.
Wow thanks a lot! Glad you found them helpful.
Wow same here let's connect
Wow same here let's connect
Thank you so much for the attention to detail in making these practical videos. They were really great resources for me to learn/review key concepts during my job search, which I was fortunate enough to land!
Is there a way for your viewers to support your work monetarily so you can continue to make these videos?
Thanks Kevin, glad you like it! Glad to hear they have helped you get a job!
Yes, I do offer paid courses on SQL and database design, which are available here: www.databasestar.com/course-list/
Thank you, very useful to me.
Glad it was helpful!
Great to see how it actually works in a real world
Glad you found this format useful!
Excelent video, regards from Caracas, Venezuela
Thanks a lot!
So much high quality information
Thanks! Glad you liked it.
This is so amazing
Thanks! I’m glad you like it.
Thank you so much
You're most welcome
Thanks for this great content! I was getting really confused with so many product attributes but you make it seem easy. I'll practice a little to fully understand and hopefully convert the database design to hibernate
Thanks, I'm glad it helped!
Thank you
You’re welcome!
I want you say Thank you. 🙏🙏🙏
You're welcome!
Thanks alot for this. I am working on mine and this is just what I needed to get it going in the right direction. A couple of questions: what are your thoughts about using visio as a diagram design layout mechanism.? question number 2: I am considering setting up a test web server on a separate metal box different from my sql box. Is there a way to have my webserver to connect to the sql server for the data storage etc or should i put them on one machine?
Thanks, I'm glad you like it!
1 - Visio is a good tool for database design. It's been around a while too. I often don't use it as the places I work don't have a license for it (and I don't use it at home), but I have used it in hte past.
2 - I think this would be possible, but the details are outside of my area of expertise. I assume you would have to allow connections on certain ports.
thanks bro
No problem!
I love you. you are the best.
Hahah thanks!
Thanks for the video, very informative!
In the context of this diagram:
- how can I differentiate which product colors are available and which are sold out and display this
- the same with dimensions. What sizes are currently available and which ones are not?
You can relate the product_variation table (which has the qty_in_stock column) to the product_item and then the colour_table (which has the references of colours) to see which items are in stock.
@@DatabaseStar won't the new relation be redundant?
SELECT
s.size_name,
pv.amount > 0 as in_stock
FROM product_variation pv
INNER JOIN product_item pi ON pi.product_item_id = pv.product_item_id
INNER JOIN size_option s ON pv.size_id = s.size_id
WHERE pv.product_item_id = 1;
I only have this idea for now
Congrats, great video! I would love if you teach us how to design a digital wallet database!
Thanks! Sure, what do you mean by a digital wallet database?
@@DatabaseStar like PayPal or Wise! Demonstrating how can you design a database that requires transactions using money on balance, credit card and stuff like that
Thanks for the great video. Hopefully , you will create a eCommerce database design that has multi vendors like amazon or alibaba
You're welcome! Good idea
Thanks, I am building the Product Data Model in Data Vault 2.0, and struggling to get the concept to be implemented in DV format. Any pointers help will be really helpful.
No problem! I don't actually have any experience with Data Vault so I can't be of much help, but I hope the concepts of normalisation would still apply.
@@DatabaseStar No worries, thanks for the response. Will keep trying.
very understandable 💯
but i think using that EAV approach it will not fit when your products are linked variations like "BLACK color SM size SILK fabric..." like that first approach would work, could it cause u can retrieve one buy one no way to linked them to be select as one choice while being a combination of different features
I think I understand your concern, but I think the query would be OK. You can get all of the product variations in one query and their variations, and display them on the page as needed.
What use application for database design
It’s called Lucidchart
excuse me, i am newbie, i want to ask the code from the video is can use on dbvear or MySQL workbench? i still difficult to used them. i am happy if you englightmen
You can use it in any editor in SQL. You may need to make some minor adjustments for each database, such as data types, but it should work.
how i insert image for productItem, example, has 5 size and 5 color -> has 25 product item???
You can insert separate rows for each option.
Sir it is possible to include product category as an attribute of product ?
Yes, you can join to the category table for this.
what name software for create database design like this video?
It’s called LucidChart.
Thanks a lot! Can I have diagram and script. Thanks
Sure! You can get them at the link here, which is also in the description: www.databasestar.com/dbdesign/
can we use jsonb fields as alternative EAV
Yes you can, it has a similar kind of flexibility.
I am currently implementing a jsonb field for a project im working on. Remember, querying such a field comes with its own downsides
how if some product dont have size ?
is the size_id on product will be null ?
It could be null, or you could have a Size value of "not applicable" and relate that.
@@DatabaseStar okay thanks
Can you make a similar video for a movie ticket booking application
Good idea, I can do that
@@DatabaseStar thank you, looking forward to it
Where can I find the SQL code for this database?
You can find it in the link in the description, which is here: www.databasestar.com/dbdesign/
Brother the script U provided in Ur GitHub repository has possible errors, I ran it in MySQL workbench but it failed saying "Error Code: 1824. Failed to open the referenced table 'attribute_type' ". Can U plx fix this ASAP as I have to practice queries on some meaningful data. Plx provide the correct script to create this database or guide me if I made a mistake.
Hi Xubi, as mentioned over email, the script has not been updated. The issue was caused by the tables not being created in the correct order.
Can you please make a practical video like this for w3school
Sure, what do you mean "for w3school"? Like, view their website and create a database design for it? If so, w3school seems to be a simple blog, is that what you're looking for?
@@DatabaseStar yes and we add more feature
Please design how to design permission based system there are mutiple actors .each actor has some permission
Good idea, I can do that.
Confirmation link not working.
Which confirmation like is this? Is it the one in the email that I send to you?
Where is the direct link to Schema?
What do you mean?
why we remove gender table
It's because we can represent the gender as a category in the category table.
You sound like a Kiwi! 😄
Haha yeah the New Zealand and Australian accents are similar!
Lets connect
Sure!
@@DatabaseStar OK what's ur social media handle sir