Jump to content

Best way to deal with item with different sizes available


Mal1
Go to solution Solved by Barand,

Recommended Posts

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?

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.).

Link to comment
Share on other sites

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 by Mal1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.