How should I store product and product image data for an online store?

Question

I'm working on a storefront application in PHP with MySQL. I'm currently storing my data in 4 tables: one for product lines, one for specific products within those product lines, one for product images, and one which specifies which images are linked to which specific products. Each specific product belongs to one product line, each product image can belong to several specific products, and each specific product can have several images. My tables look like this:

ProductLines
    id, name, description, etc.
SpecificProducts
    productLineID
    id, color, size, etc.
ProductImageLinks
    specificProductID
    imageID
Images
    id, imageFileLocation, name, etc.

It's working fine this way, but it seems like it's not very efficient for retrieval purposes.

For example, I have a page that lists each product line along with a thumbnail of a randomly chosen image from that product line. To do that I have to first query the database for a list of all product lines, then perform a separate query for each product line to get all of the specific products that have associated images, pick one of those, and then query again to get the image.

Another possibility I considered would be to use one query to get all the product lines I'm looking at, a second query to get all the specific products for all of those product lines, a third query to get all of the image links which specify which images are linked to which specific products, and a fourth query to get all those images. I imagine this would be a bit faster because of the reduced number of queries, but it would leave a lot of work for PHP to do figuring out the connections between product lines and products and images, which could be just as slow.

So my question is, is there a better way to store this data? Or a better way to retrieve it based on the database I already have in place? Or is one of the two options I've identified really my best bet?

Edit: I'm not actually storing image files in the database. The image files are stored in the file system. My "Images" table in the database just stores the location of the image file along with useful info like the image title, alt text, etc.

Answer 1

Yes - just write a single query that will retrieve all that information in one shot.

I'm a little rusty on this, but you can lookup the queries in mysql reference.

1. create a query that joins these tables on the appropriate keys
2. you need to select the first item from a subquery that retrieves the images for a specific query, and then order by rand() and select the first.

This can definitely be done in a single query. Even if it can't you can always create views which is sometimes a better way to organize your queries so that they are more readable. In other words, instead of returning the result of your query, just create a view corresponding to your first query. Then create a view that corresponds to running your second query on the result of the first query, that operates off the view. And so on. Then, your actually query can be done in one shot by retrieving from the final view.

Answer 2

Are you set on storing the images in the mysql database?

In my, similar application, I simply stored the images in /images/productimages/imagesize/productid.jpg where imagesize is "small", "large" etc, for different thumbnail sizes, and productid.jpg is the id from the SpecificProducts table

Answer 3

As far the database design goes, you have a fairly solid (and standard) design. You could combine your ProductImageLinks and Images tables as long as it's a 1:1 relationship to save some queries.

As for your product line image retrieval, you have a couple of options that would drastically reduce the number of queries required:

1. Create a new table in your database called ProductLineImages. Instead of picking the image randomly from the associated products, load a set of images in there that you can choose randomly from. It won't be as dynamic this way, but this is the most efficient method.
2. You can do all of what you described in a single (but less efficient than #1) query.