eCommerce Product Database Design: Step-By-Step

[@DatabaseStar]

Want to improve your database design skills? Get my Database Design project Guides here (diagrams, explanations, and SQL scripts): www.databasestar.com/dbdesign/?

[@ymadhurireddy]

This is one of the videos to Design a Database. Appreciate your time to make this video.

[@DatabaseStar]

Glad it was helpful!

[@Kaffeejunk1e]

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.

[@DatabaseStar]

Wow thanks a lot! Glad you found them helpful.

[@basketballskills-ur7lc]

Wow same here let's connect

[@basketballskills-ur7lc]

Wow same here let's connect

[@hoangthypham6976]

Thanks for your video I have a RDBMS final test at this week and tbh, i was so confused abt everything i've learned in my class. You help me a lots.

[@DatabaseStar]

You're welcome, glad it helped!

[@mrbartuss1]

Great to see how it actually works in a real world

[@DatabaseStar]

Glad you found this format useful!

[@Kevin-kh7xv]

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?

[@DatabaseStar]

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/

[@hammam92]

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

[@DatabaseStar]

Glad it was helpful!

[@varunkapadiya6863]

this is the best video I have seen before. thanks a lot !!!!

[@DatabaseStar]

Wow thanks! I’m glad you like it.

[@johnnychin9598]

Oh my God , this tutorial is GODLIKE. Your the best sir. Thank thank a milliooooonn thank you very muchhhhhhh.

[@DatabaseStar]

Wow thanks a lot, I’m glad you liked the tutorial!

[@JimRohn-u8c]

This amazing! Please keep making more!

[@DatabaseStar]

Thanks, I will make more like this!

[@miyingojamil6784]

I think it better for primary key to have Serial datatype(auto increasing number), unless there is a custom format being followed, like car license plate. Thanks

[@DatabaseStar]

Good idea, I usually use auto-incrementing integers (such as Serial in Postgres).

[@caophuvinhfplhcm]

Thank you very much sir, this video is really helpful. Btw, is this technique called "database custom filed with EAV model"?

[@DatabaseStar]

You’re welcome! Yes this is a kind of EAV model, the variation table and related tables are similar to that.

[@houseofmalik9944]

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

[@DatabaseStar]

Thanks, I'm glad it helped!

[@SergiiBoiko]

This is the best video I have seen before!

[@DatabaseStar]

Thanks! I’m glad you liked it.

[@fresonn]

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?

[@DatabaseStar]

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.

[@fresonn]

@@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

[@IamDoQtorNo]

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?

[@DatabaseStar]

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.

[@VenewebTV]

Excelent video, regards from Caracas, Venezuela

[@DatabaseStar]

Thanks a lot!

[@JV-pu8kx]

Do yourself, and everyone else, a favor. Set your code editor to use a mono-spaced font!

[@DatabaseStar]

Great idea! My other editors are set this way, but I've just used the default for MySQL Workbench which is not monospaced. I'll change it. Thanks!

[@JV-pu8kx]

@DatabaseStar For anyone else who is reading this: I use mono-spaced fonts anytime I am indicating, on a Web page, in print, etc., something to be typed. Can be code, file names, etc., except web and email addresses. This makes it easier to see exactly what is to be typed as the spaces are clearly visible and the I (uppercase i), l (lowercase L), and 1 (one) are, usually, better differentiated. Not always so with O (letter O) and 0 (zero), but more so than with proportional fonts. I have seen editors that would restrict the font choices to mono-spaced fonts. Why this is not so for _all_ code editors is beyond me.

[@Lykkos-321]

this is top content!! thanks a lot!!

[@DatabaseStar]

Glad you like it!

[@dota2-plays-shorts]

nice in depth explination. thanks for your efford :)

[@DatabaseStar]

Glad it was helpful!

[@codingcambodia]

Thanks for the great video. Hopefully , you will create a eCommerce database design that has multi vendors like amazon or alibaba

[@DatabaseStar]

You're welcome! Good idea

[@jsnow07grad]

This is so helpful! Thank you!

[@DatabaseStar]

Glad it was helpful!

[@joel-rg8xm]

Thank you, very useful to me.

[@DatabaseStar]

Glad it was helpful!

[@Shkkmj6868]

Thank you so much. You explained it very nicely.

[@DatabaseStar]

Glad it helped.

[@pedrogermano15]

Congrats, great video! I would love if you teach us how to design a digital wallet database!

[@DatabaseStar]

Thanks! Sure, what do you mean by a digital wallet database?

[@pedrogermano15]

@@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

[@nickwoodward819]

Awesome - but quick question if you don't mind - at what point does an attribute become a variation property, or vice versa? IE what if the website started selling hair clips that don't have a size?

[@nickwoodward819]

Ahh, it's to allow the stock level to vary based on the variation. So if I wanted the price to change based on a variation, say color, I'd have to put both the price and color entries in the product_variation table?

[@nickwoodward819]

ha, that still makes price variations based on *size* difficult though... interesting... :D

[@DatabaseStar]

Yeah that's right, it depends on how you want to track stock or prices. This is just a suggested design though.

[@nickwoodward819]

@@DatabaseStar yeah for sure, enjoying it, and your emails. thanks :)

[@mannycalavera121]

So much high quality information

[@DatabaseStar]

Thanks! Glad you liked it.

[@triplestrafe]

appreciate this comprehensive video!

[@DatabaseStar]

Glad it was helpful!

[@vishalpoddar]

How do we handle multiple kinds of product variation.17:29 Example: In the above example the product_variation table captures the variation in size , but what if we also had to capture the variation in colour as well i.e. suppose product_code should have remained same for all the size and colour combination. Should we have then moved the colour_id too to the product variation table? Then I think we would have qty for the combination of size and colour.. Also in that case I think the product variation table would not have been necessary. Thanks

[@DatabaseStar]

Good question, yes if that was the scenario then I believe the product variation would not be needed, and capturing the qty for the combination of size and colour could work.

[@YT-cd2vi]

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.

[@DatabaseStar]

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.

[@YT-cd2vi]

@@DatabaseStar No worries, thanks for the response. Will keep trying.

[@aminem2080]

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

[@DatabaseStar]

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.

[@DucHuu03]

This is so amazing

[@DatabaseStar]

Thanks! I’m glad you like it.

[@Kashmiriboy.]

Sir it is possible to include product category as an attribute of product ?

[@DatabaseStar]

Yes, you can join to the category table for this.

[@abo8esabo8es52]

Can you please make a practical video like this for w3school

[@DatabaseStar]

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?

[@abo8esabo8es52]

@@DatabaseStar yes and we add more feature

[@AmarjargalB]

I want you say Thank you. 🙏🙏🙏

[@DatabaseStar]

You're welcome!

[@wassimyazbekk]

Thank you so much

[@DatabaseStar]

You're most welcome

[@purushottamkute7485]

Can you make a similar video for a movie ticket booking application

[@DatabaseStar]

Good idea, I can do that

[@purushottamkute7485]

@@DatabaseStar thank you, looking forward to it

[@tanvu3956]

Thanks a lot! Can I have diagram and script. Thanks

[@DatabaseStar]

Sure! You can get them at the link here, which is also in the description: www.databasestar.com/dbdesign/

[@iordanistsapanidis4021]

How to Design a Database for cityguide products

[@DatabaseStar]

What’s a city guide product? I have a video on my channel about designing a database so you can follow that process.

[@7Janiel]

Hello, i am trying to create an online shop that might sell not only clothes but other products as well like laptops. How would i create the database so it can accommodate clothes and laptops for example which can have different attributes (size for clothes and processor_type for laptops). Thanks

[@DatabaseStar]

You can use the database design in this video, I think, and the product categories would be where you would have clothes and laptops.

[@juamurasaki7311]

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

[@DatabaseStar]

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.

[@djtoon8412]

can we use jsonb fields as alternative EAV

[@DatabaseStar]

Yes you can, it has a similar kind of flexibility.

[@matsberg1568]

I am currently implementing a jsonb field for a project im working on. Remember, querying such a field comes with its own downsides

[@AmirMufid]

what name software for create database design like this video?

[@DatabaseStar]

It’s called LucidChart.

[@emesaiYT]

how if some product dont have size ? is the size_id on product will be null ?

[@DatabaseStar]

It could be null, or you could have a Size value of "not applicable" and relate that.

[@emesaiYT]

@@DatabaseStar okay thanks

[@PhyoKo-kd4ks]

What use application for database design

[@DatabaseStar]

It’s called Lucidchart

[@ДенисТерентєв]

Where can I find the SQL code for this database?

[@DatabaseStar]

You can find it in the link in the description, which is here: www.databasestar.com/dbdesign/

[@huynguyenluonggia6393]

how i insert image for productItem, example, has 5 size and 5 color -> has 25 product item???

[@DatabaseStar]

You can insert separate rows for each option.

[@bilkisuismail6096]

Thank you

[@DatabaseStar]

You’re welcome!

[@X4Xubi]

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.

[@DatabaseStar]

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.

[@gc5035]

Please design how to design permission based system there are mutiple actors .each actor has some permission

[@DatabaseStar]

Good idea, I can do that.

[@Spkntei]

thanks bro

[@DatabaseStar]

No problem!

[@VeeWebCode]

I love you. you are the best.

[@DatabaseStar]

Hahah thanks!

[@mac.ignacio]

Confirmation link not working.

[@DatabaseStar]

Which confirmation like is this? Is it the one in the email that I send to you?

[@psmontte]

Where is the direct link to Schema?

[@DatabaseStar]

What do you mean?

[@basketballskills-ur7lc]

Lets connect

[@DatabaseStar]

Sure!

[@basketballskills-ur7lc]

@@DatabaseStar OK what's ur social media handle sir

[@Sedhu_17]

why we remove gender table

[@DatabaseStar]

It's because we can represent the gender as a category in the category table.

[@JosephineFahy]

You sound like a Kiwi! 😄

[@DatabaseStar]

Haha yeah the New Zealand and Australian accents are similar!