Hey all,

Sorry if this is a readily answerable question - I have searched extensively so far and have not found an answer yet.

I am designing a fairly extensive website for a client. The site features lots of media, especially pictures. I am stuck deciding how to store the URLs of these pictures.

My first thought was to simply store them in the relevant tables, i.e. product_images, designer_images, user_images, etc. But the redundancy inherent in having a dozen different image tables seems like overkill.

My other option, as I see it, is to have one big "images" table, and store everything there. I could then have an index field that would reference the image's purpose (i.e. it could read something like "prod_id_56", which would tie the image to product number 56). One concern I have is if any metadata has to be stored with the image (like this image is the primary image for product 56), it could lead to ugly extra fields with null values or a string of flags. Plus, this creates two queries (a JOIN might not always work if the application needs to run some logic to decide which image to pull).

Any advice is appreciated! I may just be overthinking this, but I want to get it right before the whole site is built around it!

Thanks,
Dave

I do like the idea of one image table. It does tend to mess up your model a bit as it will look like an incomplete wheel, a center with a lot of spokes, but then no tire to hold it together. All of those tables at the end of the spoke being the parent of the one table in the middle. Also, this means the RI would have to be handled programmatically, which may/may not be an issue for you. Unless you changed your design from the other side and did not differentiate so much on the type of image, then it would be a natural flow down to your image table.

I tend to think or images on a website as a resource
that resource could be a photo, it could be, say, a URL to further details

however at present you are looking at images... an image is an image, whether that image is a photo a drawing or whatever. So if I was in your position I'd have one table containing URL's to images or resources with a FK to the relevant product, or if you think you may need the same image for more than one product an intersection table associating the product and image ID.

you may find it sensible to sub classify URL's to identify whether its a photo or drawing and so on.

one advantage of an all in one table is, if you wish, you can drag random images for use elsewhere