I'm assembling an employee database and I have to have the ability to revise employees member information, but additionally keep an eye on all of the revisions. How must i structure the database to ensure that I'm able to have multiple revisions of the identical user data but have the ability to query against the newest revision? I'm searching at information that changes rarely, like Surname, but which i will have to have the ability to query for outdated values. Therefore if Jenny Cruz changes her title to Jenny James I have to have the ability to discover the user's current information after i search against her old title.
I suppose which i will require a minimum of 2 tables, one which consists of the uid and the other that consists of the revisions. I Quickly would join them and query against the newest revision. But must i break it even more, for the way frequently the information changes or the kind of data? I'm searching at about 40 fields per record and just one or two fields will most likely change per update. Also I am unable to remove data in the database, I have to have the ability to appreciate everyday all previous records.
I'd make use of the separate table because then you'll have a unique identifier that indicates the rest of the child records that's even the PK on the table that we think causes it to be not as likely you'll have data integrity issues. For example, you've Mary Johnson that has records within the address table and also the email table and gratifaction evaluation table, etc. Should you give a change record towards the primary table, how's it going likely to relink all of the existing information? Having a separate history table, it is not an issue.
Having a erased area in a single table, after this you need to have an non-autogenerated person id as well as an autogenrated recordid.
You might also need the possiblity of individuals failing to remember to make use of the where erased = where clause that's required for nearly every query. (Should you choose make use of the erased flag area, do your favor and hang a view using the where erased = and require designers to make use of the vista in queries not the orginal table.)
Using the erased flag area additionally, you will require a trigger to make sure only one record is marked as active.
An easy method of carrying this out would be to give a erased flag and rather than upgrading records you place the erased flag around the existing record and place a brand new record.
You are able to obviously also write the present record for an archive table, should you prefer. But when changes are infrequent and also the table isn't large I wouldn't bother.
To obtain the active record, query with 'where erased = 0', the rate impact is going to be minimal when there's a catalog about this area.
This typically is augmented with a few other fields just like a revision number, once the record was last up-to-date, and who up-to-date it. The revision number is extremely helpful to obtain the previous versions also to do positive securing. The 'who up-to-date this last and when' questions usually come when the product is running rather than throughout needs gathering, and therefore are helpful fields to set up any table that contains 'master' data.
@Peter Tillemans' suggestion is a very common method to accomplish what you are requesting. However I can't stand it.
The dwelling of the database should reflect the actual-world details which are being patterned.
I'd produce a separate table for
obsolete_employee, and merely keep historic information that will have to be looked later on. By doing this you can preserve your real worker data table neat and keep just the old data that's necessary. This method will even simplify confirming along with other options that come with the applying that aren't associated with searching historic data.
Just think about that warm feeling you will get whenever you type
select * from employee and absolutely nothing but current, correct goodness comes flowing back!