What to do with archive data, e.g. library book check-outs

[bigalpha]

In the case of a system that keeps track of checking in and out (e.g. library books), where is the archive data stored? If a person makes 30 trips to the library, obviously one record will be the current visit - but where do you keep the 29 other visits?

Do you create a separate table for old check-outs: Current_Check_Out and Archive_Check_Out?
Or do you just leave the data in place and as-is: All_Check_Outs?

 

[CJ_London]

It depends on your requirements - once a book has been checked back in, you could just delete the record, so the only current records are books checked out - no history maintained

If you are always looking at the history, just have the one history table table.

If you are normally only interested in the current situation but occasionally want to look at the history then I would have two tables (history and current) - both updated at the same time. The only reason for doing this is performance. To determine the current situation from a history table usually requires 3 criteria, not required in a current table so can be slower. OK there is a small overhead in maintaining two tables - and easier if the history is always going forwards - i.e. there are no backward corrections - but the benefit to the user is probably worthwhile.

 

[jdraw]

As CJ said - it depends on your requirements. Some organizations are interested in Client likes/dislikes so retain/gather a lot of info, and mix and match what they have gathered to suggest/offer other things to clients. Some even sell it to others.

Have you ever filled in a warranty form for a new product? Notice how they're interested in your Age, your Income, whether you drive a car or not, own/rent your home, education, children, pets......

In your case you could be interested in the types of books read by a client, how often they return to the library..

Depends on your needs...

 

[bigalpha]

Thanks for the responses. In my application (inventory management), I have a table that contains a bulk of the information and didn't know if I should just let the table grow or split it into an archive.

 

[CJ_London]

with that type of situation, I would just have the one file - but if it is getting too large you may want to consider archiving old records according to a suitable business rule-perhaps over 5 years old for current items or 3 years old for non current items - again depends on your requirements.

In finance, records need to be kept for 6 years, in HR significantly longer, but that does not mean they have to be treated as 'current'

 

[bigalpha]

with that type of situation, I would just have the one file - but if it is getting too large you may want to consider archiving old records according to a suitable business rule-perhaps over 5 years old for current items or 3 years old for non current items - again depends on your requirements.

In finance, records need to be kept for 6 years, in HR significantly longer, but that does not mean they have to be treated as 'current'

What is considered large? I'm pretty sure 'large' is considered much much larger than what I have. I have less than 3000 lines so far.

Our regulatory records limit is 3 years, but we generally keep them indefinitely.

 

[CJ_London]

That's tiny

By large I was thinking in terms of db size - once you reach 1.5gb compacted its time to think of archiving, or if user performance is degrading

 

[bigalpha]

That's tiny

By large I was thinking in terms of db size - once you reach 1.5gb compacted its time to think of archiving, or if user performance is degrading

Psht. Mine is right around 10MB compacted.

Is there some performance hit once you reach a certain number of tables?

 

[CJ_London]

Ultimately there will be but you shouldn't be anywhere near there yet.

Performance is primarily affected by indexing and depth of tables plus the complexity of your queries/form/report recordsources, use of domain and other functions, form/report design etc. Another factor is the performance of your network - slow networks will slow performance - assumes the back end is on the network

 

[bigalpha]

Ultimately there will be but you shouldn't be anywhere near there yet.

Performance is primarily affected by indexing and depth of tables plus the complexity of your queries/form/report recordsources, use of domain and other functions, form/report design etc. Another factor is the performance of your network - slow networks will slow performance - assumes the back end is on the network

I'm sure that I probably have a small and non-complex database compared to most. Most of my tables are look-up tables with scant data in them.

I try to keep everything as streamlined as possible to avoid having slow-downs. Unfortunately I have been unable to split this thing because our server access is ridiculously slow. We access another DB off of the server and it takes fooorrreevvveerrrr to do anything in it.