CODEWITHSUNDEEP
mysqlsql-serveroracle-databasepostgresqldatabase-design
Govinda Sakhare
Govinda Sakhare

Reputation: 5749

Database design: Managing old and new data in database table

I have a table Student with field as followed,

Student table (one record per student)

student_id
Name 
Parent_Name
Address_line1, Address_line2, Addess_line
Photo_path
Signature_file_path
Preferred_examcity_choice1,Preferred_examcity_choice1, Preferred_examcity_choice3
Gender
Nationality
.
.
.

I am inserting into this table on Registration form completion through the web interface.

Now there is one more module in a web interface for updating the student data, on every update request I am updating the student table records and inserting the new entry in student_data_change_request. student can change records any number of times.

student_data_change_request

request_id(auto_incr PK)
old_name
new_name
old_photo_path
new_photo_path
old_signature_file_path
new_signature_file_path

Now coming to problem, earlier students were allowed to change very few fields, now client want to allow the candidate to update more number of fields(around 20 fields) and adding old and new columns for the corresponding column isn't elegant and preferred(I guess), I will end up creating 40 columns to keep track of 20 columns. So how should I redesign my table? suggestions are welcomed.

Upvotes: 1

Views: 855

Answers (4)

Neville Kuyt
Neville Kuyt

Reputation: 29629

As usual, it all depends on how you intend to use the data.

My strong preference in these cases is the solution @mathguy suggests - embedding the concept of time in the main table design. This allows you to ask the question "what was this student's address on 1 Jan?", or "who had signature x on 12 Feb?".

If you have to report or execute business logic that reflects the status at any point in time, this design works really well. For instance, if you have to report on how many students lived in a particular address for a given term, you want to know when the records were valid.

But not all applications care about "time" - sometimes, you just want to have an audit table, so you can trace what happened over time in case of anomalies.

In that case, @loztinspace's solution is useful - but in my experience, this rapidly escalates into more work, because those who want to inspect the audit records can or should not get access to a SQL prompt on your production environment.

Upvotes: 0

user5683823
user5683823

Reputation:

I would rather have just one table, with an additional column for effective date. Then a view that picks up just the most recent row for each student_id becomes your first "table". If for some reason you must show "current" and "most recently changed" values side-by-side, that is another view.

Upvotes: 0

LoztInSpace
LoztInSpace

Reputation: 5697

One approach is to have a shadow table named (table)_xx that has the same columns, the time, date, update/insert/delete flag, user or whatever and no referential integrity. Set a trigger to update that table from the source whenever anything happens.

If you've got genuine business requirements that need history then do those properly but this pattern is great as a general audit, debugging and forensic tool.

It's also really easy to automate/script as you just generate it from the DB metadata.

Upvotes: 3

Evgeniy Kiskin
Evgeniy Kiskin

Reputation: 199

Usually historical table looks like:

request_id
column_name
old_value
new_value
dt

request_id and column_name are primary key. When you update student table you insert new entry in student_data_change_request for each updating column.

Edited: Another way: 

request_id
value_type
name
photo_path
signature_file_path
...

and insert first entry with old values and second entry with new values. Colum value_type is mark old or new.

Upvotes: 0

Related Questions