Hi folks--
I'm having a hard time deciding between design decisions that may or may not make my life miserable in six months.
I need a database to hold a significant amount of data about aging accounts. I'll add about 12000 lines each month about how much money various customers owe, and how long they've owed it (I don't have control over the fact that the raw data is calculated elsewhere, it's a BW thing and I just live with what I get).
So each month I'll have a file with:
Customer type, collector name, Customer name, amount owed 1-90 days, amount owed 91-180 days, amount owed 181-365 days---etc.
I want to be able to produce reports that tell me trends within those buckets, ie., these customer types owed this much in Nov 07 versus what they owed in Oct 07 (one month ago) and also what they owed Nov 07 versus Nov 06. Same for collector name -- how does this month compare to last, and how does this month compare to last year at this time?
So--do I want a separate table for each new month or do I want a single table with a field that indicates the "report month", which is the month that the 'picture was taken' of the data?
I hope that this makes sense. I kind of think the latter makes more sense but I'm not sure because the whole thing breaks a lot of rules (It's going to be a failure of normalization, for example, but there's nothing I can do about that....)
I'm lousy with cross tab queries, which doesn't help.
Thanks so much
Rosie
I'm having a hard time deciding between design decisions that may or may not make my life miserable in six months.
I need a database to hold a significant amount of data about aging accounts. I'll add about 12000 lines each month about how much money various customers owe, and how long they've owed it (I don't have control over the fact that the raw data is calculated elsewhere, it's a BW thing and I just live with what I get).
So each month I'll have a file with:
Customer type, collector name, Customer name, amount owed 1-90 days, amount owed 91-180 days, amount owed 181-365 days---etc.
I want to be able to produce reports that tell me trends within those buckets, ie., these customer types owed this much in Nov 07 versus what they owed in Oct 07 (one month ago) and also what they owed Nov 07 versus Nov 06. Same for collector name -- how does this month compare to last, and how does this month compare to last year at this time?
So--do I want a separate table for each new month or do I want a single table with a field that indicates the "report month", which is the month that the 'picture was taken' of the data?
I hope that this makes sense. I kind of think the latter makes more sense but I'm not sure because the whole thing breaks a lot of rules (It's going to be a failure of normalization, for example, but there's nothing I can do about that....)
I'm lousy with cross tab queries, which doesn't help.
Thanks so much
Rosie