Laravel - Products DB Design (variations, attributes, categories & more)

Hi there guys.

I am new to the forum. It's the first time that I am doing a big project (ERM & CRM), and many decisions have to be made by me. Kind of feeling anxious already. I am working on my Products Module, and I would like to make the DB design optimal. We're going to need a lot of different data and some flexibility cause of the nature of our products, and the SEO guys are already pressing for an SEO dedicated table.

I have made a DB schema for the products, variants, categories, attributes, and metadata ( Check the link below for the schema image ). Not expecting a full solution/code or soever!

Whoever has experience on this topic, I'd be happy to hear some feedback on the current schema, tips, or anything that would help make it better.

I don't have that much experience designing such database relations, and the imposter syndrome kicks in. For now, I'd like to focus on database design. Later on, how am I going to insert and update this thing? It's another subject. Thanks in advance!

Here is an image of the current schema

ibb.co/Rp7G8vV <-----

siangboon

4 years ago

Level 54

i think your description cover quite number of topics and each topic also can be very broad, in short the question is not clear or specific.... It may hard for people to answer and if answer may need to write long answer which seldom people will do it here... perhaps ask in different threads for each topic or problems may easier to get reply from the experiences folks here.

anyway, my suggestion is find some similar products as references and study how their structure it. it may easier... i do not know any CRM/ERM, but for product wise, perhaps can have a look on ecommerce system such as opencart...

nicolas_vl

4 years ago

Level 1

@siangboon Hi there! Thanks for taking the time to read and comment on my post. I think the topic it's pretty straightforward. I don't want to sound disrespectful, but did you open the image from the schema I've made so far? I can't link with HTTPS:// cause of my freshly made profile, but I've made the URL bold so that no one won't miss it.

I am not excepting someone to design me a database. I'm seeking just a tip or a piece of advice on the already provided schema. So i can go on knowing that I'm in the right path or do it again. I have done my research on various platforms. Saw some interesting & complex approaches from some and from others, designs that will only cover an end client using a theme and selling 200 products. Sometimes, just researching and inspecting code is not enough. A lot of anti-patterns out there, which still serve a purpose for the given solution. I even tried MongoDB, but I think normalized & structured data is the way to go based on the other modules and functionality that we have to build. Cheers :) :)

Griehle

4 years ago

Level 8

First I want to say welcome to laracasts. There are so many helpful useful people in here, I am not one of them. But there are a lot. It sounds a little like you need to breathe. It seems big big chunk it out. and even though I cannot believe I'm saying this after giving someone else crap about it on twitter. Do the from scratch series. They rule. Everything you want is in there. Alo, don't let the SEO guys push you around. They are just marketing. Not to Market until you build it.

martinbean

4 years ago

Level 80

@nicolas_vl Your schema seems OK for the most part. One suggestion I would make though is to make SKUs its own entity (i.e. create a skus table) instead of having “variants”. Because you have some redundancy where you have a product_sku column repeated in multiple tables.

In merchandising, a SKU is a particular variant. If you have say, a t-shirt that comes in many sizes and many colours, then each size-and-colour combination would have a separate SKU. Red/Small would have a SKU. Red/Large would have another SKU. Blue/XL would have yet another SKU. So one product would have many SKUs. This way, you only need a sku column in one table.

Please or to participate in this conversation.