I need some help to understand the "best" way to design a database (Postgres) which stores multiple products with multiple images.
To be more clear I put an example:
I have a table for "car", one for "pet", another for "toy". Now I need to associate to each of that tables multiple images, and I have no idea how to do it in a efficient way.
Do I have to create an image table for each of them, same as "car_image", "pet_image" and "toy_image" with a foreign key?
Or maybe is possible to have a single (generic) "image" table with multiple foreign keys?
Thanks for your help.
Dave Tolls wrote:The image table wouldn't have the foreign keys.
Your car table (eg) would (assuming many-to-many) have a mapping table car_image with a pair of ids, mapping a car to an image for each row.
my needs was to have a one-to-many relation between car (or toy or pet) and images. Anyway if I understand correctly, in your example I should have something like this:
Dave Tolls wrote:.......
What does the database as a whole represent?
The real database I am (trying to) working on is about industrial refrigeration machines.
Machines are grouped by field of application, and they are very different from each other, sharing just few to none attributes, "image" is one of that.
So i have tables Condenser, Compressor and Evaporator. Each Condenser (or Evaporator or Compressor) can have one or more images, but those image(s) are just for that specific Condenser.
Now if I use multiple foreign keys in the Image Table, I will always have null fields. Is it a good/recommended practice or there is some better way to achieve what I need?
Sorry for the dumb questions but I am just a beginner, and sorry for my poor English.