Database structure for multi-product e-commerce platform

Ecom
1

Hey everyone! I’m stuck on designing the database for my first online electronics store. I’ve got the basic structure down, but I’m scratching my head over how to handle different product types.

My shop will sell stuff like computers, TVs, phones, and tablets. Each type has its own specific details. For example, a laptop needs information like CPU and graphics details, while a TV requires specs such as screen size and display type.

I’ve counted about 38 different product categories, each with unique specifications. At first, I thought about making a separate table for each product type, but that would be a lot of tables. Is there a smarter way to do this?

Here’s a quick example of what I’m working with:

CREATE TABLE laptops (
  id INT PRIMARY KEY,
  model VARCHAR(50),
  cpu VARCHAR(30),
  ram INT,
  storage INT
);

CREATE TABLE tvs (
  id INT PRIMARY KEY,
  brand VARCHAR(50),
  screen_size INT,
  resolution VARCHAR(20)
);

Imagine doing this for 38 different types! It feels overwhelming. Any ideas on a more efficient approach? Thanks for your help!

2

Hey there DashingDog! Wow, 38 product categories? That’s quite a challenge you’ve got there! :sweat_smile:

I totally get your struggle with all those tables. Have you considered using a more flexible approach? Maybe something like an Entity-Attribute-Value (EAV) model? It could help you manage all those different product types without going crazy with tables.

Here’s a thought - what if you had a main ‘products’ table, and then separate tables for attributes and values? That way, you could add new product types without having to create new tables every time.

But I’m curious - how are you planning to handle product searches and filtering with so many different attributes? That could get tricky, right?

Oh, and have you looked into using JSON columns for storing some of the more variable attributes? I’ve heard some databases handle that pretty well these days.

What’s your take on performance vs flexibility? I’d love to hear more about your specific needs and what you’ve tried so far!

3

hey dashingdog, have u thought about using a hybrid approach? maybe combine a main products table with some JSON columns for those unique specs. could save u from makin tons of tables. just watch out for searchin and filterin - might get tricky with JSON. what do u think? any specific performance needs?