strategy for removing or keeping ex-clients

[merlin777]

I'm designing a database that records patient appointments for 25 patients and 10 therapists.

What i'm wondering is the best approach for dealing with ex-patients. Here are my considerations:

• if a patient leaves, they are unlikely to return and if they do it would be easy enough to add them again.
• if i delete them (there will be a table for patients linked to the main appointments table) then all their old appointments would be deleted too (or would they)
• that saves space and speed but someone may want to look back through old appointments
• if i don't delete them, the list of patients will get longer and full of redundant entries and increasingly inconvenient to pick from lists

is there a strategy for dealing with this scenario?

[mrojas]

I would consider keeping, among other tables, two sets of tables, one for active and one for inactive patients. The first set, call it the master, would have a Yes/No field indicating if patient is active. If this field is set to yes (True), then you move the record, copy to other set of tables, then delete from master.
When adding a patient, check first the master and then the Inactive set of tables. If you find it in the Inactive, prompt the user if he/she wants to retrieve information and act accordingly.

[merlin777]

thanks - is that a common approach?

[ssanfu]

if i don't delete them, the list of patients will get longer and full of redundant entries and increasingly inconvenient to pick from lists

Deleting inactive patients would be bad in my opinion. You could be asked about the patient in 2 to 3 (or 4 or 5) years.

I would add an Active (or Inactive) status field in your patient table. That way you only have to search one table. It also doesn't destroy the FK links and you don't a have to try and move the patient back to the patient table - just check (or uncheck) a check box. Plus, it is easier for reporting - to determine which patients were seen (active) in a year.

that saves space

Not something you really have to worry about nowadays.

if i delete them (there will be a table for patients linked to the main appointments table) then all their old appointments would be deleted too (or would they)

Yes they would be. Or should be - otherwise you would have a lot of orphan records in the related tables.

[June7]

I agree with Steve - one table with status field. I am of the opinion that deleting records should be a rare event.

[merlin777]

thanks guys