[SOLVED] Database design issues

[nealo]

So my head is now exploding.

I'm pretty new to this php mysql stuff but have had experience in other scripting languages and I'm trying to learn as i go on. However I've met a dead end. My families business is designing and selling tee-shirts and we have decided to go online.

Now how to explain, the customer will choose a garment --> gender --> design --> colour --> size. The problem arises as we make the shirts once they have been ordered so say the customer chooses

 

shirt --> mens --> design one --> white --> medium

 

this will decrease the stock number of the medium mens white teeshirts by one

 

shirt --> mens --> design two --> white --> medium

 

and this, i want, to decrease the same stock number but i can design the database to do so. The only way i can figure out is to have separate tables for every design, one with the garment-gender-design-colour and one with the sizes but this will mean having the medium mens white tee-shirts numerous times so that each design would have its on individual stock.

 

If you understand, its not been easy to put into words, i am grateful for any help

Nealo

[rcorlew]

I would set up my db like this using one row per each individual item, don't worry about having 100 rows or so, many db's have tens of thousands of rows.

 

Here would be the column names for your 1 table:

 

s_id, gender, size, color, style, quantity

 

then you could run a simple query like this to select shirts:

 

$sql = "SELECT * FROM shirts WHERE gender = '$gender' AND size = '$size' AND color = '$color' AND style = '$style' AND quantity > 0";

 

then after a shirt is purchased, run a query to update quantity.

[nealo]

Thanks for the reply. I agree there is no doubt, that would work to the extent that i could cope with it but It's just that the same shirts are used for all the designs, that way would mean say if the stock of white shirts in design one reached 0, there are white shirts left unused in the other designs if that makes sense.

Nealo

[nealo]

ok i think ive done it now. if i had table with the colours, colour_ID, colour. Then link the designs each with a size to the first. just means ill have quite a few tables.

See you all later when i need help with the PHP. how do i mark it as solved?

Nealo