I have just started designing my structure for a mysql database that will contain a product catalog for a photohgraphy site. I am struggling how best to structure the design though.
Each photograph item will have a Title (i.e Lakehouse), Category (i.e Scenery), Description (i.e blah blah), Price (i.e £120), Sizes (i.e 20x30, 25x35, 30x40 etc…) and Colours (i.e Red, Blue, Black etc…)
I have tried splitting the various fields into different tables to then join together with the below tables currently set up:
You’ll need one or two tables to link colors and sizes to the photographs. Or do all photographs come in all colors and sizes?
And do all colors and sizes have the same price? Or is there a standard calculation to get the price for each size from the ‘standard’ price in the products table?
oops sorry i made a slight mistake with the price, that should go in the size table yes. So it would be the below instead with the prices within the size table. The colour does not effect the price.
Not all photographs come in all colours and sizes, for example the New York Canvas comes only in Blue and Red, and in 20x30 and 30x40, where as Lakehouse comes in all colours and all sizes.
Sorry if i didn’t make it clear - this is my first time at structuring databases with multiple tables!
7721 ¦ New York ¦ Description ¦ Other Value
3991 ¦ Lakehouse ¦ Description ¦ Other Value
3991 ¦ Summer Field ¦ Description ¦ Other Value
3991 ¦ Farmhouse ¦ Description ¦ Other Value
4551 ¦ Florida Beach ¦ Description ¦ Other Value
Size and colour is not related in any way. The only thing that determines the colour and size is the product itself. The colours specified for each photograph will be available in all sizes specified. So my final tables would be these 6 with no more needed:
7721 ¦ New York ¦ Description ¦ Other Value
3991 ¦ Lakehouse ¦ Description ¦ Other Value
3991 ¦ Summer Field ¦ Description ¦ Other Value
3991 ¦ Farmhouse ¦ Description ¦ Other Value
4551 ¦ Florida Beach ¦ Description ¦ Other Value
In terms of then querying the database - if i wanted to display the photograph Lakehouse on a php page with a dropdown option for the customer to choose the size and colour, would I use the INNER JOIN method to join all 6 tables together?
How could i go about combining the size query with also the colour query so that i only have to do 1 query for everything instead of for each? (If that makes sense? I just want to ideally limit the amount of queries to the server to as few as possible)
You’d have to join the sizes table to get the description of each size to display in the drop down. Size ‘01’ wouldn’t be very clear for your average customer
And if the id’s and the descriptions in the sizes table and the colors table are of the same types, then you might use a UNION like this:
SELECT
'size' AS type
, ps.sizeid
, s.description
FROM Product_Sizes AS ps
INNER JOIN Sizes AS s
ON ps.sizeid = s.id
WHERE ps.id=3991
UNION
SELECT
'colour' AS type
, pc.colourid
, c.description
FROM Product_Colours AS pc
INNER JOIN Colours AS c
ON pc.colourid = c.id
WHERE ps.id=3991
Of course, looping through the result set you’ll have to check if you’re dealing with a size or a colour.
great thank you guido, is there a quick or easy way of checking whether it’s a size or colour within the loop or would i just have to use if and else PHP conditions?
An if…else… would work fine.
You could also load everything in a multiple array, and then loop through the $array[‘size’] and $array[‘colour’] arrays, but maybe that’s overkill.
