Archiving Data

[LilMorePlease]

I've redesigned a database that's primary purpose is to assign batch numbers
to users. There are two main forms, an Assign Batch form and a Delete Batch
form.

My question pertains moreso to the Delete Batch form. I have MasterBatch
table that contains all of the valid batch numbers and an AssignedBatches
table that keeps track of assigned batches. When a batch is deleted in the
form, the Batch Number stays intact in the MasterBatch table, just the
information associated with the batch gets deleted in teh AssignedBatches
table. The batch numbers are therefore recycled, as a new user can then be
reassigned to it.

I want to keep track of batch history, that being the unassigned batches. I
created an additional table called DeletedBatches and append the data to it
when the user deletes a batch in the Delete Batch form.

I am wondering if this is really the best way to keep track of old data (a
table within the main database) Should I be appending the data to a log
file? I've only heard of log files and don't really know what options I have
for doing something like that.

Comments greatly appreciated.

AA

 

[Damian S]

Hi LilMorePlease...

The question is why are you recording something in your database then
deleting it, and worse still, giving the SAME number to another set of data...

If you want to keep archive data, perhaps you could simply add a flag to the
Batch table that indicates whether a Batch is Active or not, then instead of
a Delete Batch function, have a "Make Inactive" or "Archive Batch" function
that sets the flag to Inactive (or whatever).

How does that grab you?

Damian.

 

[LilMorePlease]

The question is why are you recording something in your database then

deleting it, and worse still, giving the SAME number to another set of data...

I never thought about that before. What an excellent point! I asked around
and I guess it's been out of laziness and the fact that another ID in the
system that is used for employee identification purposes gets recycled as
well. I have a meeting today and I will propose changing this. That way,
1/2 of the problem will be fixed. Then the employee id that gets recycled
will be the only gremlin left to be tackled.

If you want to keep archive data, perhaps you could simply add a flag to the
Batch table that indicates whether a Batch is Active or not, then instead of
a Delete Batch function, have a "Make Inactive" or "Archive Batch" function
that sets the flag to Inactive (or whatever).

How does that grab you?

Excellent suggestion Damian. If in the event that they run out of a
particular type of batch number, they could reactivate the old ones, first
saving teh records to a file somewhere....

Now you got my wheels a turning.

 

[LilMorePlease]

So guess what...I had a meeting and my idea has been vetoed. Apparently they
like the fact that numbers get reused. They are even happy that my recordset
to assign batches pulls in the top X number of batches where the batches at
the top of the list are used more frequently than the ones at the bottom.

::shrug::

 

[LilMorePlease]

I meant to say Your Idea. I did give you credit too and told them the story
about how I was posting about the proper way of storing history and that you
seemed shocked that we were recycling batch numbers.