best practice for handling redundant data?

[merlin777]

I'm writing an appointments database. Staff will enter an appointment between themselves and a patient. There will be a table of staff, another of patients and another for appointments which will inclue staff id and patient id.



Here's my question: if a patient or member of staff leaves what is the best way to handle it? If i delete their records then reviewing old appointments will have that person missing from them but if i keep them those lists will just get huge with turnover and viewing a list will be unhelpfully long.

I considered leaving the records but flagging them as current or ex so just the current can be listed.

Is that a good approach?

[pbaldy]

Yes, typically a status field of some sort is used. That lets you exclude people who have left during data entry.

[merlin777]

thanks for that.

[pbaldy]

No problem, post back if you get stuck.