Win a copy of Five Lines of Code this week in the OO, Patterns, UML and Refactoring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Multiple products and multiple images

 
Greenhorn
Posts: 6
Netbeans IDE Postgres Database Java
  • 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.
 
Marshal
Posts: 67415
173
Mac Mac OS X IntelliJ IDE jQuery Java
  • 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?
 
Rancher
Posts: 4612
47
  • 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
Netbeans IDE Postgres Database Java
  • 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
Rancher
Posts: 4612
47
  • 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
Rancher
Posts: 4612
47
  • 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
Netbeans IDE Postgres Database Java
  • 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
Rancher
Posts: 4612
47
  • 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
Rancher
Posts: 4612
47
  • 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
Netbeans IDE Postgres Database Java
  • 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.
 
Blueberry pie is best when it is firm and you can hold in your hand. Smell it. And smell this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic