Database design

[mat]

I am doing a little fake project to help me learn php basics. It's supposed to just be a small online sotre selling some product, consists of about 5 different product ranges and each has a dynamically generated product gallery with thumbnails of all the products in that range. You can click one and if so up pops a detail.php window with a bigger version image and detail for that product:



See the "sizes available" bit, i would like that to only show as many rows as there are sizes available for that particular product? but this make the database more complicated doesn't it?

the only way i can see it is if i have a table just for sizes available and that each and every size is it's own row. Here is what i am thinking for the database table layout:

product_collection
collectionID [ INT(2) ]
collectionName [ TINYTEXT ]

products
productID [ INT(2) ]
productName [ TINYTEXT ]
productImage [ TINYTEXT ]
productNumber [ TINYTEXT ]
collection [ INT(2) ]

sizes
sizeID [ INT(2) ]
size [ TINYTEXT ]
product [ INT(2) ]

So that means if there are 4 different sizes available for a particular it will have 4 entires (rows) in the "sizes" table.

what do you think of this?

mat,

[Ökii]

I would try to serialize/implode all the different sizes to makes one string that can then be added to a 'product parameters' field in the database. Then when the string is returned, you can unserialize it and run a for loop to output all stored data.

$var=array("12foot by 8foot","3inches by 6inches","much smaller");
$var=serialize($var);
$addme = "INSERT INTO.... VALUES('$var')";
--------------------------------------
$getme = "SELECT var FROM.....";
$var = unserialize($var);
for($va=0;$va<count($var);$va++) {
echo $var[$va].'<br>
';
}

Fly-typed code - ergo untested.

[ritap]

I strongly disagree with Okii's suggestion. This is so against database design principles. You never want to concatentate data together. By doing this you loose the ability to easily query on the individual pieces of information. You could do a complicated like query with the concatenated info but the performance would suffer.

The correct way would be to make a seperate table with the allowed values.

Rita

[Spookster]

I concur with Rita. I design databases for a living.

Here is a decent little tutorial to give you an introduction into data modelling with relational databases:

http://www.surfermall.com/relational/lesson_1.htm

[mat]

so you think my original design was o.k?

i'll take a peep now at this tutorial

mat,