Image galleries - seperate table?

[whatson]

e.g. you have a used car web site, and want to display multiple images of each car for sale.

In the CMS you can upload multiple images. Should the location be in the same table as the car details, e.g. Model, Manufacturer, Images.

Is it ok for the image locations to be in the one column images, e.g. interior.jpg, engine, jpg, wheels.jpg

Or is it better to make a new table called images, and just have the columns Image and Car, so you can match the image location with the specific car?

[matrix_jan]

Whatson,

If the number of images is standard, like you mentioned: interior, engine, wheels, etc. then you can create a table with those details as columns. Then for each listing you will have the location of the image stored in columns...

Or if you have 20 images for listings you can list 1 to 20 as columns.

Someting like this.

Listing# | interior | engine | wheels
=====================================
123 | img/123/int.jpg | img/123/eng.jpg | img/123/wheels.jpg
124 | img/124/inte.jpg | img/124/en.jpg | img/124/wheel.jpg

It's another thing if you want those images to have titles...

Hope I gave you some ideas...

[whatson]

Right I understand, but no, there could be 3 images or there could be 10. I guess some of them may have descriptions too.

[matrix_jan]

Then I guess the easiest way would be creating maximum number of columns(maximum number of pics you can have in one listing), and having null as default, if the field is set then there is an image. And have another similar table for titles... There are ways for storing multiple data in one field, but it's too complicated and not recommended. And assuming you have thousands of listings, you wouldn't want to create a table for each listing...

[swa66]

Just make multiple tables in your database, use the relational aspect!

Just refer to the car the picture belongs to by id.

[jadebox]

I'd suggest using a single separate table for the image information. It should have columns like the following:

Car_ID: The primary key of the car
Image_Type: A value enumerating the type of image (interior, exterior, etc.)
Image_File: The path to the image

The above allows you to dynamically add or change the types of images and to easily perform queries like "get all images for a specific car" or "get all interior images."

[swa66]

If the types of images are well defined, that in itself would be a good table just as well.

In essence: you want your database to be normalized.

Ref: [databases.about.com...]