Question 361913 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How do SQL history tables work when there are foreign keys at play?

Lets say I have 2 tables:

User:

ID: PK, AI
username: varchar
pwd: varchar
addressID: FK references address.ID

Address:

ID: PK, AI
street varchar,
houseNr varchar,
postal varchar,

How would the history tables look like since we obviously have foreign keys.
The requirement: we must be able to see all the addresses that were ever linked to a user.

The way I see it is that if we update user, a new entry goes into the user_history table.
That entry must point to an address, we can’t point to the address in the address table since if that would change we would have the new data instead of the one at this point of time.

So how I see it on update of address we must also duplicate the address to the address_history table so our user can reference that foreign key to always remain historized.

How would it happen in practice?

Thanks in advance,
Kind regards,

Tags:

2

Answers


  1. 0

    In every case I have seen implemented in a real-world database, the reference is the other way around:

    User:

    ID: PK, AI
username: varchar
pwd: varchar

    Address:

    ID: PK, AI
userid: reference to User
street varchar
houseNr varchar
postal varchar
is_current bool

    So addresses may come and go, but all addresses used by a given user reference that user’s id. Old addresses that are no longer used by that user only need to set is_current=false, but the record still exists permanently.

    It also still works if old address records are moved to an Address_history table; the foreign key in the address record still points to the former owner by its user id.

    Another advantage for switching the reference the other way is that a given user may have multiple addresses that are current.

    Login or Signup to reply.
  2. 0

    The Address_History table should have a User_ID foreign key referencing User.ID.

    ID: PK, AI
User_ID: FK references User.ID
street varchar,
houseNr varchar,
postal varchar,
date_replaced datetime DEFAULT current_timestamp

    When you replace a user’s address, you copy street, houseNr, and postal to the new Address_History row, and set its User_ID column to the user’s ID.

    User_history also has a FK to User.ID.

    ID: PK, AI
username: varchar
pwd: varchar
addressID: FK references address.ID
orig_userID: FK references User.ID
date_updated: datetime DEFAULT current_timestamp
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search