I am trying to make a database that follows a form that the company uses. When a client walks in the members of staff have to fill in a form and the data is recorded. The form on paper is 10 pages long. The first time a client is seen the entire form is filled in and the client gets a clientID.

I have split the form into sections that make sense like accommodation and employment. I know I can link these tables together with the clientsID. That's the simple part.

Now when a client returns the form comes out again but this time only certain parts are filled in, what ever the clients needs are. The records for most parts don't need updating but a new record needs inserting. what would be the best way around this.

So at the moment I have for example a table called client with an id and name another table called accommodation with clientid and address and another table employment with clientid and employer.

But how do I go about it when a client comes in to let us know he has a new employer. I cant update the current one as that is needed but I would need to add new record for the client.

Would this mean I would have to add a look up table for all my current tables?

  • 1
    It would be helpful to include a diagram of the current schema... and is only the information of the current employer which needs to be retained ?
    – Thronk
    Mar 30, 2013 at 16:30

2 Answers 2


Another option is to just add StartDate and EndDate fields to your "Employment" table. Then when a client comes in with a change in Employer you update the EndDate for his previous Employer and add a new record for the new Employer with no EndDate. You can always look up his current employment by checking which record for that ClientID has no EndDate. If you don't need start dates or end dates you could just add a field that's basically a flag to indicate whether this is present or past employment.

  • Yes, an "employment history" table sounds like a good approach. If the company wants details on prior employment if the client has been in their current job for less than a month, or for a bunch of other circumstances, the design has that covered. Jun 3, 2013 at 7:09

One option is to update the current one, but keep a history table with dates of updates and values at the time of the update. For example, MSmith altered Employer on 2013/01/31 from IBM to Cigna.

This would allow you to know the employer at a certain time as long as the table which requires this relation also has a datetime column

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.