On Wed, 2004-05-12 at 15:19, Dalton Calford wrote:

> Hi Paul
>
> Paul Schmidt wrote:
>
> >On Wed, 2004-05-12 at 12:05, Fred Pratt wrote:
> >
> >
> >>Excellent info! The 40gb table limit will be a small problem for us, but not
> >>insurmountable.
> >>Thanks much,
> >>Fred
> >>
> >>
> >
> >A 40GB table comes about for 1 of 2 reasons, first it could be not
> >normalized enough, or there is too much data. Any good SQL book should
> >deal with the first. I think the second needs discussion.
> >
> >
> >
> The data records are extreamly normalized, and small, the record size
> being only 186 bytes.
> Each record fully encapsulates a phone conversation.

Hmmm small record, high record count......

>
> >What often happens, is it's not too much data, it's too much data in
> >"hot" storage. Most of that data should really be in "cold" storage.
> >Take invoices for example, do you really need 10 years worth of invoices
> >immediately available? Probably not, once they are paid, only
> >government or company auditors might ever look at them. So it doesn't
> >really matter if they take a while to access. Printed copies waste
> >trees, but electronic copies don't. A Good way to get archival copies,
> >is to print them to PS or PDF files and ship them off to a service
> >bureau for printing on microfiche, the fiche is good long term storage,
> >and it will likely still be usable in 50 years let alone 10.
> >
> >
> >
> I agree with you, as per the origional emails notation of natural
> breakpoints.
> Unfortunately, depending on the time of year, we can achieve 40 GB of
> data, with only 90 days of information in hot storage.
> This is all within a single billing period, for a single province (we
> span a few).
> Unfortunately, people seem to want to keep making phone calls. Each
> call generates a record, even those that did not connect.
>

Ah, a phone company, your biggest competitor (Bell) uses a very
expensive data warehouse running on Big Iron Unix cluster to do the same
job, I don't want to tell you what they use, in fear of being sued, once
sub-contracted to a sub-contractor to a contractor they used.....

I expect this past weekend generated quite a few, and that Christmas is
utterly ridiculous.......

>
> >Another item with invoices, is what data is required, it may simply be,
> >invoice number, customer number, total before tax, each tax total, total
> >after tax, and paid total. So storing all of the invoice detail may not
> >be required either.....
> >
> >
> >
> Our customers have a tendancy to question each and every line item on
> thier bill, and our customer service people have had to perform line
> item adjustments for up to two previous billing periods (some people
> don't open thier bills immediately, or read them a few months later.....)
>

Too bad you guys are in Ottawa, I'm looking for new opportunities right
now, and unfortunately I'm stuck in Toronto until my wife gets her
Masters Degree.

Paul