Re: Case Study: Do-Not-Call List

alcesteatxmissiondot_at_com.or.net wrote:

>On a private forum, a discussion came up as to the optimal way to store
>the U.S. National "Do-Not-Call" list in and RDBMS.
>
>[ For those out of the US and who
>don't pay attention to U.S. news, this is a list of 40 million nine-digit
>telephone numbers. Number are divided into three segments: a three-digit
>area code that is indicative of their geographic areas (mostly an area has
>one area code, but some have two or three overlapping ones) a prefix that
>indicates their local exchange, although those aren't well-defined in many
>cases, and then four addition digits which have no meaning other than a
>unique identifier. Not all area codes are used and many have special meaning.
>Area codes are not geographically distributed, i.e. 5-- area codes are not
>all in the midwest. AFAIK, area codes don't cross state boundaries.]
>
>This list must be checked against by telemarketers before calling the
>number in question.
>
>I imagine this list would be used in one of three ways:
>+checked for a specific number prior to a phone call
>+anti-joined against call list to remove numbers permanently from call list
>+anti-joined against call list for the specific block of numbers on a
> telemarketer's daily list
>
>I imagine most telemarketers do numbers near to each other during blocks
>of calling time, although I don't know the business that well.
>
>List are updated quarterly. As far as I know, no numbers are automatically
>eliminated from the list, although I'm not sure if that's been established.
>
>So, given such a simple table...what would you all recommend doing with it?
>The choices that could make some sense are:
>
>+One number column standard heap table with PK on number
>+Two number column standard heap table with area code and rest of number
>+One number IOT
>+Partitioned table by area code (~200 partitions)
>+Partitioned table by state (List partitioned)
>
>My gut reaction was to go with the first form, and possibly put it on a
>read-only tablespace.
>
>What do you think? I'm not supposed to get the list legally because I'm not
>a telemarketer...
>
>Jer
>
>

The phone calls are almost never made by people ... they are made by computerized equipment.

But my first instinct would be IOT partitioned only if required for performance which I doubt
would be an issue. Insert need not be fast. Updates never happen. Deletes ... a fascinating issue
because over a finite period of time every number gets on the list because even if someone new
gets the number ... no one ever calls and says take me off the list.

Perhaps the better solution would just be to outlaw telemarketing and let them go find honest work.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)