CODEWITHSUNDEEP
databasehibernatejpa
Wudong
Wudong

Reputation: 2360

Database design consideration on foreign-key both way between two tables

I have two class, User and Status, while a user will have a number of status, this is naturally Many-to-One relation and can be easily mapped to DB Tables.

However, the requirement also need User to maintain its “current” status, i.e., in the User Table, it need to have a foreign-key to the Status table. The result of this is having two foreign-keys between two tables in the opposite direction.

One obvious problem of this is once the records are inserted into the two tables, I can delete neither of them cause deleting from one table will violate the other table's foreign-key.

What is the best Design for this situation?

Upvotes: 2

Views: 279

Answers (2)

Taoufik Mohdit
Taoufik Mohdit

Reputation: 1931

Do you have to keep the status in a separate table ? can it not be just represented by a java enum, and the User would have a status property. Something like this:

public enum UserStatus {
        X, Y, Z;
}

public class User {
    private UserStatus status;
        ...
}

Upvotes: 1

planben
planben

Reputation: 700

  1. in your Status table , add a column that will determine whether this status record is "Current" or not. ** for performance issues , you can set only the "current" status records with '1' value and the rest with null value
    1. you now don't have to use 2 foreign keys , only one - from Status to User.
    2. if you are using hibernate as the post's tag :) you can create a view in the database that will select only the "Current" status records and will have the same structure as the Status table.
    3. connect the view to the User entity using One-to-One relation,

I hope it helped you !

Upvotes: 1

Related Questions