Table Structure/Relationship Stumper

[HemanSkeletor]

Hi guys,

This one is actually not very hard, probably.

I inhereited a database that's used to generate form letters to retired employees. It has a standard employee info table, then the way the designer chose to record when letters where sent to each of the retirees strikes me as odd.

Each time we run off letters, he appends, to a table called something like tblLetterDate, the date the letter is sent, and the hundreds or THOUSANDS of SSNs (foreign key). The date is repeated a bazillion times.

So you have this MASSIVE table that looks like this:

555-12-1234 | 7/10/98
555-12-9999 | 7/10/98 etc., with hundreds of repititions of the dates.

We want to record the date these letters are sent and to whom. What is the most efficient means of doing this, please?

THANK YOU!!

--HS

 

[MichaelRed]

Leaping off hte limb, I can only decalim numerous caveats re blind leaders.

Having declaimed muchly, ... Depends a bit on what exactly you (your org) is trying to accomplish -on different levels, but it doesn't seem all that horrific. It would make sense if you need (or just wat to) track all occurancs of 'spamming' each individual. Of course this assumes that some other record includes at least a reference to the actual correspondence document sent, but otherwise it may depend on what you need. If it is ONLY to know when hte most recent correspomndence was issued, it is somewhat more than necessary, but the cost of a few Megs of storage seems less important than the availability of complete recordkeeping.

A more limiting issue, at least for me, would be how much processing is done with the (presumably LARGE) recordset w/ X * 100K records, since Ms. A. is not known for efficiency in processing.




MichaelRed
mlred@verizon.net

 

[HemanSkeletor]

Michael,

That's just it (that processing is clunky): this is a database that sends people correspondence that they need to get retirement benefits, and this one table alone is in the hundreds of megs. It's a group that has well into the 100s of 1000s of retirees.

There MUST be a more logical means of recording these transactions.

All this thing does, near as I can tell, is generate this one letter. Then we run that append query to tell us when ol' 155-55-5555 got his letter!

Thanks

--HS

 

[Crowley16]

hmm, well, you're not giving us that much information to go on really...

if the id number and the date are the ONLY pieces of information that is relevant, then I suppose one way would be to shorten the date field...
you can use like a autonumber thing on dates, i.e. 01/01/01 = 1, 02/01/01 = 2...

then you'd just be storing a lot of 1's and 2's instead of a full date serial... but this won't be hugely reducing in size, and after about 10 years (assuming letters are sent each day) it won't make much difference at all...

your best bet is probably to upsize to a larger database like oracle/sqlserver...