update values with history in database

I have got a next situation. In my database, I got 2 tables and one relationship between them:

Document

PK_Doc

FK_Employee

Text

Employee

PK_Employee

Name

SurName

So, when I update(delete) an existing employee the document must have the values of an employee 开发者_运维问答that existed at creation time. How should I do this?

Upd 1:

For example: I am creating a new document(PK_Doc = 1) and choose in combobox Employee (Edward Norton). Then Edward Norton became Edward Harrison. So, when I open a document (PK_Doc = 1) current value of combobox with employee should be Edward Norton

You need an Employee_History table containing at least the following columns

PK_Employee_History_Id Employee_Id (not a constraint) Name Surname ValidFrom (Date)

On Employee creation/update/delete scenarios you create a new record in Employee_History - I would use a SQL trigger for this.

Point your Document at the immutable Employee_History table record rather than the mutable Employee record.

In other words, you need to deal with a few slowly changing dimensions. Bony suggested type 4, but you've other options. See also bi-temporal databases in case you need a precise audit-trail.