Best way to deal with item with different sizes available

[Mal1]

I'm looking for opinions on the best way to deal with database tables/structures when you need to have an item with various columns of data but for each of these there's various size options available.

 

In my instance it's rugs (could be a rug with x, y and z properties... the same rug, same design, fabric etc but that rug or id can be available in 120cm x 180cm, 160cm x 230cm, 200cm x 300cm as an example with different levels of stock in each) but the same issue could apply to clothing I'd imagine.

 

Think of something that's S, M, L, XL in terms of clothing... I'd guess it's a pretty standard way of doing things but my site the way it is is running extremely slow and I think how it's been done may be the cause. So what do people think should be the standard way of doing it?

[Jessica]

The way that I would do it would be to have this type of structure:

products table

product_id -- pk autoinc

name

description


attributes table

attribute_id -- pk autoinc

name -- things like size, color, etc. (When I bought my living room rug there was one main rug in 4 color patterns, and 4 size options, so 16 total options)


attributes_options

attribute_option_id -- pk autoinc

attribute_id

attribute_option -- colors or sizes, "blue", "L", etc.


products_attributes table

link_id

product_id

attribute_id

attribute_option_id

details -- optional field for comments.

If you don't get it, try googling data normalization.

[shlumph]

I'd imagine there would be two tables: items and stock.

 

items:

- id

- name

- description

 

stock:

- id

- item_id

- size

- quantity

[Mal1]

I'd imagine there would be two tables: items and stock.

 

items:

- id

- name

- description

 

stock:

- id

- item_id

- size

- quantity

That's pretty much what we have, all the description in one table then another table with length and width and quantity in another (presumably linked with product id).

 

Perhaps the database structure isn't what's the issue at all, which is what our programmer said. The SQL query may be a mess but I couldn't see what was wrong with two tables and still don't (unless there's an issue with linking them/keys etc.).

[Jessica]

Do a DESCRIBE on the tables, and check if you need to add more indexes.

 

You can also post the query here and we can see.

[Mal1]

In saying that there are actually a number of tables for sub-fabrics (i.e. wool -> wool, wool & viscose, wool & silk...), shapes, colours etc which I'd imagine should be flattened out as much as possible?

[Mal1]

Do a DESCRIBE on the tables, and check if you need to add more indexes.

 

You can also post the query here and we can see.

 

Do you mean DESCRIBE individually each table? Or is there a way to do it for all tables/specific tables?

[Mal1]

Rugs Table
 

Field Type Null Key Default Extra

id int(11) NO PRI NULL auto_increment

name varchar(50) YES MUL NULL  

mod_trad enum('Traditional','Modern','Either') NO   Either  

type int(11) NO   NULL  

subtype int(11) YES   NULL  

design_name int(11) NO   NULL  

fabric int(11) NO   NULL  

subfabric int(11) NO   NULL  

fabric_old int(11) NO   NULL  

pattern int(11) NO   NULL  

shape int(11) NO   NULL  

designer int(11) NO   NULL  

description text YES   NULL  

group int(11) NO   NULL  

main_picture int(11) NO   1  

views int(11) NO   0  

colour1 int(11) NO   0  

colour2 int(11) NO   0  

colour3 int(11) NO   0  

stock_override tinyint(1) NO   0  

kids tinyint(1) NO   0  

supplier varchar(100) NO   NULL  

weight decimal(10,2) NO   NULL  

cost decimal(10,2) NO   NULL  

profit_margin decimal(10,2) NO   NULL  

discount_factor decimal(10,2) YES   NULL  

deleted tinyint(4) YES   NULL  

active int(11) YES   NULL

 

 

rugs_stock Table:

 

Field Type Null Key Default Extra

id int(11) NO PRI NULL auto_increment

rug_id int(11) NO   NULL  

width decimal(10,2) NO   NULL  

length decimal(10,2) NO   NULL  

price int(11) NO   NULL  

discount_price int(11) YES   NULL  

discount_price_override int(11) YES   NULL  

cost_price decimal(10,2) YES   NULL  

vat float NO   NULL  

stock int(11) YES   NULL  

active int(11) YES   NULL

 

 

Unfortunately there are various tables that have options for those highlighted in red (fabrics etc.) that are linked to the rugs table.

 

 

I think this was done so that each of these options could use a pre-loaded and editable drop-down for options when entering details but I doubt this is how it should have been done?

Edited February 26, 2013 by Mal1

[shlumph]

Do you mean DESCRIBE individually each table? Or is there a way to do it for all tables/specific tables?

Just the relevant tables.

 

 

The SQL query may be a mess but I couldn't see what was wrong with two tables and still don't (unless there's an issue with linking them/keys etc.).

If you could post the queries, that would be good as well.  You can also do EXPLAIN SELECT... (rest of query) in your MySQL editor to see what could be causing an issue.  There may be cross joining going on, which is really taxing on resources.

[Barand]

I don't see an index on the rug_id in the rug_stock table

[Mal1]

I don't see an index on the rug_id in the rug_stock table

 

Astounding... added an index and the time is at least twice as fast! Can't believe that wasn't in. Makes you wonder!

[Mal1]

Got it down from 13-15 second processing time of queries to 3-5 seconds simply by clicking to add an index to the rug_id on the rugs_stock table.

 

Thanks! Just goes to show one simple thing being over-looked can cause huge problems!

 

Added a few index to fabrics, main_photo and (particularly design_name) on the rugs table and it's now processing in roughly half a second! We had a php programmer saying that the tables an structure was a "rats maze" and the entire site needed re-coded and charged accordingly. Makes you wonder about some people and if it was left out intentionally when first built.

[Jessica]

The tables don't look *that* bad. It makes me wonder what your guy would have done if he has trouble with understanding the current structure. :/