Need help on efficiancy on a structure

[knowj]

Basically im building an e-commerce system and each product will have multiple colours and each colour will have multiple sizes also each size will have a stock value assigned to it.

this is roughly how i've planned to do this currently:

product table:
ProductID

colours table:
ColoursID
ProductID [foreign key]
ColourName
Imageurl

sizes table:
Uniqueid (a completely unique id for each product which will work when putting products into the basket)
ColoursID
SizeName
SizeValue
StockValue

Does anyone have any knowledge on efficiency of PHP/MySQL?
As the other option would be to use serialized arrays and a csv of sizes which would also make the basket process more complex (more processing power on the PHP side of things but less on the database).



footnote:
The reason i have decided to work this way is because they are different sizes and different size measurements (s,m,l etc... 8,10,12 etc...)


All opinions are greatly appreciated

[scragar]

MySQL is faster to iterate then PHP(using something like a while or foreach loop)...

on the other hand making a table that has 3 feilds, one for size, colour and product would proberly increase efficiency for processing this info in PHP afterwards. if your concerned about it so much try out each, and look at time/cpu usage(of the server).

[knowj]

Well if i used my first structure it nearly all of the processing would be done by the database (Inner joins) the only thing the PHP would be needed for would be to output the data

But if i was to use serialized arrays it would put alot of the processing onto the PHP

I just didn't know if putting too many inner joins would put unneeded stress on the database.

and if there was anything that was seen as "best practice" for this type of system

[knowj]

This is my current structure (sorry about the poor diagram) :eek:

Is there much i could do to this to improve it?


Thanks for the help greatly appreciated.

edit:
The product images table has been removed at there will only be 1 image for each colour (a detail image of each colour will not be required)

[NogDog]

I'd put a good-sized bet on the database being faster than PHP when it comes to dealing with the relationships. That's what a DBMS is designed for and optimized for. Just make sure you have indexes on applicable table columns (those that will be used in WHERE or JOIN clauses, for example).