Critique My DB Design Please! :)

[suarve]

Ok, so I've recently been assigned the task of adding some extra features to our in house ecommerce platform. My brief was as follows - "allow users to optionally add their own products attributes, unlimited in number for any product. Attibutes may or may not change the final product price. The admin backend must remain clean and easy to use".

I'd like people to basically critique this and suggest downfalls to my proposed structure - download here (png file)

Some sample data for each table:

attribute
1 - Size
2 - Colour

attribute_value
100 - 2 - Gold
101 - 2 - Red
102 - 1 - XXL

attribute_product
89 - 102 - 5.99

product
1 - Widget - 55.99


At some point I'd like to include functionality to accomodate stock levels for combinations of attributes - but that's for another day

 

[topdog@OC]

I would be tempted to just merge attribute_value and attribute_product tables together.

I'm assuming there's a 1:1 relationship between those two tables and if a record exists in one, it'll always exist in the other. You're never going to have an attribute_product record with a null attribute_value_id are you?

So merge them by sticking 'value' into attribute_product, drop attribute_value, and then you only have the one table in the middle to resolve the many:many relationship between attributes and products.

Unless there is a business rule/requirement for it that isn't disclosed, that's as far down as the data normalization needs to go to perform optimally.

If there was a need to have an attribute with a value, and no product association at all, I can only imagine it is for holding some pre-determined default value or suggestion; in which case that just needs to be a column on the attribute table.

 

[Tetsujin]

Either I don't get it, or topdog doesn't get it, but I don't see how you could merge those.

You have these things;

1. products
2. attributes (as in, Color, Size, Weight)
3. attribute values (set range of values for each #2 which could be re-used across multiple products; as in, you don't want 500 entries called "blue")

which all require their own table, then you need one extra one to link which product has which attribute values (implicitly too, which attributes) allowed to be changed on it.

Seems fine to me.

 

[topdog@OC]

I wasn't seeing the attribute_value table as being like a pre-populated lookup list of values to select from (when I should have done, the example data was there). My mistake and you're right, the schema works as is.

You'd just have to make sure I guess that if a user wants to change their entry from Blue to Navy, that it doesn't change the existing attribute_value (you don't want the other 499 ones suddenly switched to Navy too) but adds a new entry to the attribute_value table if needed and switches to that.

On the admin side it may be feasible to have it so that a change applies to all, in case of things like typo's etc.

 

[suarve]

You'd just have to make sure I guess that if a user wants to change their entry from Blue to Navy, that it doesn't change the existing attribute_value (you don't want the other 499 ones suddenly switched to Navy too) but adds a new entry to the attribute_value table if needed and switches to that.

That's a really good point, will need to look into that I think.

Am just looking into changing the design to allow for combinations of attributes E.g. Colour: Red, Size: XXL, Stock: 99 etc.