Table bloat & when to delete records question

[LilMorePlease]

I created a database that keeps track of assigned batches for users. My
question pertains to the process of deleting batches. I have a batch table
which is like a master list of all of the batch numbers and an assigned
batches table which obviously keeps track of assigned batch information.

I am working on a delete batch form. It won't delete the actual batch
numbers, just the record in the assigned batches table. I am wondering how I
go about preventing bloat in the employee and manager tables. For example,
if I've just deleted the only assigned batch for Bobby Smith, should I delete
the employee record as well, and then check to see if his manager is tied to
any other assigned batch and if not, delete that record in the manager table?
Or is there a better approach to cleaning up employee and manager records?
I don't believe cascade deletes will work because the employee & manager
tables are primary tables, not the assigned batches.

One problem I see in deleting emloyee records in addition to assigned
batches is that if the user makes a mistake when creating a batch and then
deletes it, they will have to re-create the employee record again. I suppose
I could have a message box in the delete form telling the user that the
employee currently has no assigned batches and asks them if they want to
delete it or not. Would that be the best method?

The other thing I was thinking I could do is just have a cleanup utility
that the user could access through the switchboard. Every so often the user
could go in and clean it up, thereby deleting all managers and employees that
don't have related records in the assigned batches table.

Suggestions and advice are greatly appreciated,

AA

 

[Allen Browne]

The answer to your question will depend on how the database is used, i.e.
there is not a specific answer which is the correct relational solution. So,
it's up to you.

Having said that, I am personally super-cautious about any code that deletes
info from a database. The data entry time is the largest investment a
company makes in any database, so you don't want to delete anything that
even might be useful. Other things being equal, I would vote for leaving the
data in the database.

Your last suggestion sounds much better, i.e. a report that identifies the
orphaned records, or perhaps an interface that makes it easy to remove them.
But even then, the historical data is usually valuable, e.g. to compare this
year's figures with 5 years ago.

 

[LilMorePlease]

Thank you Allen. You've helped me to make an informed decision. I
appreciate that.

Regards,
AA