[FIXED] what should the relationship be on three entities--Complaint, Reporter, Reported

Issue

I'm designing a DB for a complaints service.

Main entities: Complaint, Reporter, Reported.

Reporters can add multiple complaints and the reported have multiple complaints attached to them using the same phone number.

The Reported entity has the information about every reported numbers such as phoneNumber, status, etc. The Reporter entity has the information about the reporters, phone number, name, etc. Every reporter can submit multiple complaints about multiple reported numbers. All these complaints should be saved in the Complaint entity, and every reported number can have multiple rows each with different types of complaint.

How should the relationship between these three entities look?

What I was thinking:

@Entity
@Table(name = "complaints_tbl")
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class Complaint extends BaseEntity{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "complaint_Id", nullable = false)
    private Long complaint_Id;

    @NotNull
    @CreatedDate
    @Column(name = "issuing_date", updatable = false)
    private LocalDate issuingDate;
    private String message;

    @NotNull
    @ManyToOne
    @JoinColumn(name = "reporter_Id")
    private Reporter reporterId;

    @NotNull
    @ManyToOne
    @JoinColumn(name = "reported_Id")
    private Reported reportedId;

    @NotNull
    @OneToOne
    @JoinColumn(name = "operator_Id")
    private Operator operator;

    @NotNull
    @NotBlank
    private Enum Type;

    private int complaint_Counter;
}

@Entity
@Table
@Getter
@Setter
public class Reported extends BaseEntity{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "reported_Id",  nullable = false, insertable = false, updatable = false)
    private Long reported_Id;

    @NotBlank
    @NotNull
    private String reportedPhoneNumber;

    @NotNull
    @NotBlank
    private String status;
}

@Entity
@Table
@Getter
@Setter

public class Reporter extends BaseEntity{

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "reporter_Id", nullable = false, insertable = false, updatable = false)
    private Long reporter_Id;

    @NotBlank
    @NotNull
    private String reporterPhoneNumber;

    @NotNull
    @NotBlank
    private int numberOfComplaints;
}

Solution

I found that In order to do the right design I will have to join the columns from reporter and reported entities using Id as a foreign key.

In order to make this design, in the complaint entity we should include the following :-

 @NotNull
    @ManyToOne
    @JoinColumn(name = "reporter_Id")
    private Reporter reporterId;

    @NotNull
    @ManyToOne
    @JoinColumn(name = "reported_Id")
    private Reported reportedId;

But using Id can be a little bit confusing so instead I could use phoneNumber for Reporter and Reported as the foreign key.

Answered By - DarkBot
Answer Checked By - Timothy Miller (JavaFixing Admin)