Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I am trying to make a database that follows a form that the company uses. When a client walks in the membes 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. Thats 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?

share|improve this question
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 at 16:30

2 Answers

up vote 0 down vote

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.

share|improve this answer
up vote 0 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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