Using Single or Multi Databse For Each Year ?

[NiceMan331]

hi
i designed an accounting system which consist of : databasse and interface file
it is working well , but i wish to get your advice regarding year end colsing , in my mind i have 2 options :
1- the one which i dont like is to split the databse file to each year , means 2007 seperate , 2008 seperate
and also 2009 is seperate , then , from startup form in interface file the user should select the yaer to connect to its databse
here i have the relink code which could relink the interface file to the path of the selected databse
this may happened many times every day , i afried from repating many relink every time if will corrupt the file
2-the prefferable option for me is to keep all records for all years in one file , but here
every year i have around 100,000 records , after 2 or 3 years the file will be heavy , also the reports will be very slow
because of using functions to get the balances
pls advice me for the best solution
regards

 

[pbaldy]

Personally I would keep the data in one file. Sooner or later you'll want to do year to year comparisons, etc, which will require it. If the database gets too big, you could consider moving the data to a more robust back end such as SQL Server, MySQL, etc.

 

[NiceMan331]

thanx
that will be better
but can u lead me to the suitable approche to study SQL Server ?
ok there is SQL Server section , but i need practice lessons for it
regards

 

[The_Doc_Man]

Another thought is that you have to check with the folks who ask for these comparisons of older data how far back they usually want to go. That is, how old is "too old to keep around any more" ?

Then develop a schedule in which you archive older entries. Set aside some down time for maintenance - during which you tell your users KEEP OUT AND STAY OUT.

You must have an archiving database separate from the main one. You move the records in steps. First, add a flag to each main-table record, could be just a Yes/No flag, that says "Ready to be archived." Then you run an update query based on the age of the record that sets the flag when it reaches the age at which it becomes unlikely to be used. Then open your archiving database and do an append from the main database table to the corresponding archive table but append only the flagged records. Then close that database. Then run a delete query to remove the flagged records. Then do a compact and repair operation.

OK, now you can bring the database back online to users.

When you want to search the main database for comparisons, you just search the main DB. But if you need an older search, you can maybe use a "special" search button that looks at the archive database too.

We have a similar if not identical problem with our Help Desk. They keep records for a long time, but eventually we lose interest for old problems. So we archive to our secondary database. We can still look up an old ticket but we normally stop at two years for our trouble tickets. Our site's system maintenance period is monthly, so that is when we do the cleanups for this database.

 

[gemma-the-husky]

i dont think it will be a problem having several years data, properly indexed

the 100000 records will be what, mainly Sl/PL/NL transactions?

mostly these will be indexed into transactions hanging off a particluar account of one sort or another, and the effective number will be less - ie a few hundred or thousand at most

i have delivery ticket files with many years data, with 50000 items+ per year, and absolutley no problem with speed of reports, etc

if it does get to be a problem, just archive off some of the older years.