0

I have table A:

@Entity
@Table(name = "A")
public class A{

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(nullable = false, updatable = false)
    private Long id;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "message_id")
    private Message;
}

I have a table Message:

@Entity
@Table(name="message")
@Getter
@Setter
public class Message{

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(nullable = false, updatable = false)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "account_id", foreignKey = @ForeignKey(name = "FK_account_to_message"))
    private Account account; //and then search by Account.id
}

In my service I get all A objects by:

List<A> aObjects = ARepository.findByMessage_Account_Id(user.getId())

So I want to get all A objects which have such Message fields that Message fields have such Account fields that Account.id == user.getId()

Now I have additional type of A entity - without any relation to Message because now object A can be created by uploading an Excel file (reading data from a file and saving in database, so in this case we don't have Message data that can be added in Message column). So I have to store new A objects in A table but with empty column Message BUT with additional column -> user_id -> currently that's the parameter to find all A objects (ARepository.findByMessage_Account_Id(user.getId())) The question is how/where to store A objects that differ in Message column (empty, no relationship to Message table).

I thought about Single Table Inheritance with Discriminator value but in this case when I want to find all A objects by user_id I would have to prepare one bigger select like: get all A objects from A where A.Message.Account.id = user_id or A.user_id = user_id.

Is it a good approach? Or maybe should I use something different?

Matley
  • 1,953
  • 4
  • 35
  • 73
  • 1
    It's difficult to answer the question without knowing the business context. However, wouldn't the simplest solution be to add `userId` (or an association to the `User` entity) to `A`, and simply accept the fact that some `A`s will have no associated `Message`, while others will have no associated `User`? – crizzis Dec 13 '20 at 19:43
  • 1
    As far as the business context goes: let's say you add a `B` entity with `userId` and no association to `Message`. Do `A` and `B` have *different behaviours*? Are there any use cases when only using `B` makes sense? Or is `A` as good as `B` in every context? If that's the case, it doesn't make sense to have separate entities – crizzis Dec 13 '20 at 19:47
  • Just sometimes A objects are created from Excel file and then we don't have Message data that we could add to the column Message when creating A object. SOmetimes A object is created from reading an email (and then we have Message data to save in Message column... That's it). Additionally user_id is the currently logged user so mabye you're right that I should only add new column user_id to A table. But then what sql query should I prepare to get all A objects for some specific user_id... – Matley Dec 13 '20 at 20:44
  • 1
    You'll need something similar to the one you mentioned, with an `OR` statement. There's no way around it, though, inheritance or not. You could also consider simply populating `user_id` from the `Message` (if present), reducing the number of conditions to just one. This, of course, introduces redundancy, but the redundancy is only harmful when the `Message.account` and `userId` fields can change (because then `Message.account.userId` and `userId` could go out of sync), and judging from the source for the `Message` field, I'm assuming they can't – crizzis Dec 13 '20 at 21:16
  • OK, Thank you for your answers... I'll just add new column in A table -> user_id – Matley Dec 14 '20 at 00:21

0 Answers0