HELP! joining column vs. additional table

Hibernate ORM
1

Hi!

I need help with creating appropriate relatio between entities.
The idea is to have one Photo entity which can be “attached” to more than one main entity (Product, Manufacturer, Provider, BlogPost…)
Initially I made relation using joining column:

@OneToMany(fetch = FetchType.LAZY)
@JoinColumn(name = "product_id")
private List<Photo> photos;

which creates product_id column in photo table.

With each next entity I get another column (manufacturer_id, provider_id, blogpost_id…) and now I’m not so sure is it the right way. Total amout of main entities is about 10 and growing which means I’ll have 10 or more columns with mostly null value execept one of them per row.

Should I abandon this approach and let Hibernate create interconnect tables for each entity?
Or something else?

Thanks!

2

IMO this design is ok and join tables should only be used if you have many-to-many associations or if really, really want to keep the things separate.
One possible alternative design that allows to decouple Photo from the rest of the model is to “inline” the photo ids into the various “main entities”, by using a basic array/collection type that was introduced in Hibernate 6.1 e.g.

@Column(name = "product_ids")
private List<Long> photoIds;

Then you’d just need to query the photos by ids separately, but the models would become independent, also on the table level. A downside to this is, that you don’t have foreign key constraints that protect you, which comes with its own set of issues, but now you have options to choose from :slight_smile:

1 Like
3

Thank you very much for your answer and advice!
Partially I have this kind of solution, but not at entity level.
In the frontend when you click e.g. “New Product” you get a form to fill and an option to add photos. When you upload photo, there is no product ID to be applied to the photo so I manage a list photoIds and when you click save button, this list goes with request. Once my backend creates a product entity, I get those photos and add them to the list.
Thanks again, this give me another option how can it be done.

P.S. Also I have onClose event on frontend window which makes rest call “cleanup()” for the case you clicked “close window” or “cancel” - avoiding orphan photos. I know it’s not ideal soludtion, but it works okay for most of the time. Also I have admin option to check consistency when I walk through all the photos and check are they connected to any entity and is there approprieate file on filesystem.