Hey everyone! I’m usually working on the front end but I need some help with database design for my e-commerce project. I’m a bit lost here.
My online store has products that can be made up of other products. Like how a bike has wheels, a frame, and handlebars, which can all be sold separately too.
I’m wondering:
-
How should I set up my Product table to handle this? Should I have a field for related product IDs? Or maybe a separate table?
-
I also want to add discounts. Some will be for single items, some for these complex products, and some for whole orders. What’s the best way to structure the Discount table?
Any advice would be super helpful! Thanks in advance!
For your Product table, I’d recommend a self-referential structure. Include a ‘parent_id’ column that can link to other products. This way, your bike can reference its components, and you can nest as deep as needed.
As for discounts, consider a Discounts table with columns for ‘type’ (single, complex, order), ‘value’, and ‘is_percentage’. Then create a DiscountApplications table to link discounts to products or orders.
One challenge you might face is inventory management. When a bike sells, you’ll need to update stock for all its components. Consider using database triggers or application logic to handle this.
Have you thought about how you’ll display these complex products on the frontend? That’s another interesting challenge with this setup.
hey daisy! for products, try a separate table for relationships. like ‘ProductComponents’ with foreign keys to link stuff. it’s neat and tidy.
for discounts, make a ‘Discounts’ table with a ‘type’ field (single, bundle, order). then another table to apply 'em.
btw, how’re u handling inventory? sounds tricky with complex products. good luck with ur project!
Hey Daisy_Whimsical! Your project sounds super interesting. I’m no database expert, but I’ve dabbled a bit with e-commerce stuff before. Here’s my two cents:
For the Product table, have you thought about using a tree-like structure? You could have a ‘parent_id’ column that points to another product if it’s a component. That way, your bike could point to its wheels, frame, etc. It’s simple but might work for what you need!
As for discounts, maybe you could have a separate Discount table with a ‘type’ column? Types could be ‘single’, ‘complex’, or ‘order’. Then you could link it to products or orders as needed.
But here’s what I’m really curious about - how are you handling inventory for these complex products? Like, if someone buys a bike, do you need to update the stock for wheels and frames too? That sounds tricky!
What made you decide to build an e-commerce site with such complex products? It seems like a fun challenge!