I am a beginner and this is my first real project. The below image shows what I have been trying to reach. But it got way complicated so fast and confusing. So I'm asking if there are different ways to design the tables easier with few keys in mind:

1. attributes won't duplicate.

2. colors,sizes,materials tables have different structures e.g.(colors have name_ar,name_en,and path for the image of the color).

3. I wasn't using SKUs in the beginning, But now I think I may need them to separate.

4. Different colors may have different sizes and so on

. But after that I couldn't think of another way to reach the result that I wanted.

Thanks for all the answers in advance

CodePudding user response：

It looks like you're trying to do EAV architecture here, but don't really understand the point \ problems with it.

The point of EAV architecture is to build the database vertically vs horizontally. It looks like you're just creating a bunch of pivoted attributes, vs actually EAV design. What this means in practice is that instead of:

Table: Shirts
Columns: ID, Label, Size, Color, Weave, Fabric, Made in, Class, Discount blah blah blah

You Want:

Entity:
ID:1, [Label:'Cool Shirt', Price: 19.00, Other required properties]

Then your attributes tables

attribute:
entity: 1
attribute:type
value: shirt

attribute:
entity: 1
attribute:color
value: red

attribute:
entity: 1
attribute:size
value: large

The point of this style of architecture is that not all entities need all attributes, and you don't wanna make a super long table with every potential property an entity could possess.

You can go further down the pivot tables which is what it looks like you were trying to do here with the pivoted ID \ naming:

attribute_values:
entity: 1
attribute:3
value: 5

Table attribute_map:
id: 3
value:"color"

Table property_map:
id:5
attribute:3
value:"red"

Regardless few major things.

1. Parent should be a property of the subtables themself that's the 'entity' part. No need for another table mapping relationships. Also makes it a lot easier to run down recursive calls to get what you want as the final answer)

2. Things like price don't (usually) fit into this model well and should also be assigned as a column on the top tier entity as well. Keep that in mind when you're deciding how to structure your top tier entities.

3. Make sure to add composite indexes to all of your pivots.

Edit for what you wanted what you're trying to do:

So keep in mind every entity is just a collection of properties.

Enitity 3: Shirt, White, XL
Enitity 4: Shirt, White, X

Shirt-White-X is the "SKU" as far as your database is concerned in EAV.

You just recursively progress down all of the tree until all properties \ sub-properties are fully expressed.

Keep in mind an "entity" is just an amalgamation of properties which in this case you also want to operate as a unique key (basically a SKU). Bonus you can use unique indexes to enforce it.

From (exactly) what you said you're just trying to work out inventory, which can be done a few ways. Generally you'd just make another tier (guessing as I don't know what you're actually trying to do).

Table: Inventory

Id (store): 1
Attribute (product_id): 4
Value (inventory): 7

You can create a higher tier entity like a store and actually create product objects assigned to the store. Really depends on how your system is modeled and how far down the rabbit hole you wanna go.

This is getting a bit more into abstraction \ what you're actually doing \ how your overall architecture works.

It's rarely useful to be an EAV purist. Properties like price that will be attached to every product entity are usually easier just to add directly to the top tier entity. You're not trying to re-build magento here and there's some major downsides to pure EAV architecture as it gets more convoluted. It can be very flexible. It can also create insanely inefficient systems depending on how it's built (hello medical industry).