0

I am trying to design a table for maintaining USER information. Here is the table design as per the below image

enter image description here

Since the table has created_by and last_updated_by columns and they are the foreign keys to the user_id column of the same table, I am having multiple issues especially with Hibernate. When a user is signing up to the application, created by will be same as user id. Similarly, when the user is modifying his profile, the last updated by will be the same as user id.

How best can i tweak this design so that i still have the information about who created this record and updated it.

Naveen P.G.
  • 9
  • 3
  • 1
    Can you explain it a bit more? You already have enough information (both who created and who was editing it last). – Seti Jan 13 '16 at 15:32
  • 2
    If you want an audit trail, then define a history table for `user_info` and insert new records into the history table using triggers. – Gordon Linoff Jan 13 '16 at 15:32
  • @Seti the problem is there will be data like user_id=1 and created_by=1 which is like self referencing data. When used with Hibernate framework and version for optimistic locking, this gives issues. – Naveen P.G. Jan 13 '16 at 16:38
  • 1
    @GordonLinoff, problem with that approach is how to get the information of the user who creates it? – Naveen P.G. Jan 13 '16 at 16:39
  • @NaveenP.G. . . . A history table would naturally include the user who made the modification and the modification time (and perhaps other information). – Gordon Linoff Jan 14 '16 at 02:27
  • Then allow them to be set to null. Then if DO NOT put createdBy for users that created account themselves. As for last edited - it can reference itself - that not a problem - because logically creating your account isint modifying it - therefore newly created account shouw have created and modified nulled, then 1st edition should change last updated to correct number. Also createby would be null for self created account and integer for other users that created given account. – Seti Jan 14 '16 at 13:10
  • @Seti thanks for the detailed explanation. But even if the last edited is referencing itself, it is still a problem. You cannot even delete that record in the normal way. You have to turn off the FK check in DB to bypass it :) There are more issues when I use Hibernate. – Naveen P.G. Jan 14 '16 at 16:27
  • Set the foreign key to : on delete cascade, albo null. That way it will fix that. (As i REALLY dont rememebr the command, i would like to introduce you to HeidiSQL - very nice graphical client for MySQL and MSSQL and soon PGSQL (probably). You can ,,click'' throu the interface and change/create the references and check which ones suits you best (for foreight keys) – Seti Jan 15 '16 at 19:31

0 Answers0