Win a copy of Java 9 Modularity: Patterns and Practices for Developing Maintainable Applications this week in the Java 9 forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Multiple products and multiple images  RSS feed

 
Moreno Cavallaro
Greenhorn
Posts: 6
Java Netbeans IDE Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all.
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?
Or.....
Thanks for your help.
 
Bear Bibeault
Author and ninkuma
Marshal
Posts: 66256
151
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why do you feel that multiple tables can't refer to the same image table?
 
Dave Tolls
Ranch Foreman
Posts: 2996
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Moreno Cavallaro
Greenhorn
Posts: 6
Java Netbeans IDE Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bear Bibeault wrote:Why do you feel that multiple tables can't refer to the same image table?


My first thought was to do something like this:



But I am afraid that it could lead in a lot of null fields (the fk's), specially in case I need to add other entities with photos.

 
Dave Tolls
Ranch Foreman
Posts: 2996
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Car -< Car_Image >- Image

Where:
Car - id and other stuff
Car_Image - car_id, image_id
Image - id, image, other bits
 
Dave Tolls
Ranch Foreman
Posts: 2996
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So you have one Image table, and multiple mapping tables to handle the Many-to-Many relationships.
 
Moreno Cavallaro
Greenhorn
Posts: 6
Java Netbeans IDE Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.


Hi Dave,
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
Ranch Foreman
Posts: 2996
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If it's one-to-many then the foreign key is on the other table:

Car - id, image_id, etc

and no mapping table.
 
Dave Tolls
Ranch Foreman
Posts: 2996
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh, hang on.
You mean the other way round, don't you!


Do the other tables have any similarity to each other?
What does the database as a whole represent?
 
Moreno Cavallaro
Greenhorn
Posts: 6
Java Netbeans IDE Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!