Ideas For a Newspaper Delivery DB

[KevinM]

A friend of mine who runs a newsagent has asked me to create a 'Newspaper Delivery database'.

Whist it seems straight forward enough there is one major problem...the sheer number of records that will accumilate in the transactions table.

There are approx 300 customers who say have a paper every day (there could also be weekly, monthly mags etc).

This would amount to some 9000+ records a month if I do it the correct(?) way and as his harware is a good few years old, I'm not sure it could handle such large amounts of data.

Trans Table
Round/RouteID
CustomerID
PeriodicalID
QTY
Cost
DeliveryDate

(Delivery charge will probably be added as a 'line item')


As a customer's 'order' is fairly static is there any other way that I can change the design of the above trans table to reduce the number of records.

Problems are...

Price changes from day to day (sometimes newspaper publishers run 'special offers' for one day/week only)

Record history for audit/historical reasons.

Some customers like to pay their bill weekly although the norm is monthly which could cause billing problems with certain periodicals.....
i.e. weekly, fortnightly, monthly, quarterly and maybe even annually mags.

Any ideas for a simpler/better way?

Many Thanks

 

[FoFa]

I guess the real question would be does your friend need all the history? You could either setup a purge to be run and remove anything older than xxxx or have them control it with remove anything between date1 and date2.
One other though it appears just from this glimpse that the tables are designed correctly, and you are really not storing all that much data in each transaction. It would appear a few longs (assumption) and QTY must be an INT or LONG, AMT a single or double (or currency) and a date. So you could also ARCHIVE the data to say a diskette every so often and keep the DB size from growing out of wack, and they still have the data. A 1.4 Meg diskette could hold a good number of transactions in say CSV format.

 

[mission2java_78]

I guess the real question would be does your friend need all the history? You could either setup a purge to be run and remove anything older than xxxx or have them control it with remove anything between date1 and date2.
One other though it appears just from this glimpse that the tables are designed correctly, and you are really not storing all that much data in each transaction. It would appear a few longs (assumption) and QTY must be an INT or LONG, AMT a single or double (or currency) and a date. So you could also ARCHIVE the data to say a diskette every so often and keep the DB size from growing out of wack, and they still have the data. A 1.4 Meg diskette could hold a good number of transactions in say CSV format.

I personally dont think that many records is going to amount to much taht access cant handle.
Then again maybe the best thing to do is to purge each month.


Jon

 

[Crilen007]

I deal with tables that are 900000 lines, its a little slow on a 2.4, but once it loads its fine. So 9000 is nothing.

 

[KevinM]

Thanks for the replies.

Yes, I don't think it'll be too many and Access can certainly cope.
I'll archive them monthly or quartley.

 

[simongallop]

Due to the different payment schedules, would suggest that as part of the routine that records the customer paying their bill, that you then move the relevant records from the main table into a paid table. This can then be exported on a weekly or monthly basis as a csv / excel file. This will then be easier to look at from an accounting point of view (for the shop) and also it will prevent the accidental deletion of items that are way overdue.

HTH