[thelist] Null Performance

Michiel Trimpe Michiel Trimpe" <info at juvex.com
Tue, 18 Jan 2000 13:22:42 +0100


Hi,

    I have to begin setting up a _very_ large database, which will end up
somewhere in the terabytes but that will start at about 150 gig. Now I was
wondering how to design it properly. It'll be for a cable company offering a
couple of services to it's customers. The system would be for
administration+call management. Short explanation :
There are 1.5 million customers. Employee + Ticket + History tables are left
out of this equasion. Each customer has one or more of the following
"assets" :
Cable Access (necessary)
Cable Modem / Internet Connection
Cable Internet Email Account ( multiple per connection possible )
Cable Telephone
Cable PlusPack

We were thinking of one of the following setups:
1.A large regular Customer table with the standard info everybody has,
  possibly also Cable Access, because it's obligatory, will hold the
Customer
  Data. Then this data will be linked to one of the 5 different asset type
  tables meaning that each type has it's own table. These are flexibly
defined
  in another table.
2.To reduce the management etc. we were also thinking of another setup.
  In this situation we would have one Assets Table, and it would include all
  possible options for all five types. In other words, One row of Assets
would
  contain all of the options for Cable Access , Cable Modem etc. etc. etc.
The
  fields that wouldn't apply would be set to NULL.
3.Take the _very_ flexible approach and make an argument sub-table that
would
  contain a row for each property of for example Cable Telephony. So there
would
  be no real Assets table content, and each argument would be stored in the
  Arguments table. We kinda wrote this one off due to speed problems.
  A sample set of rows ( just to make it clear ) would be :
Arguments: ID | Asset ID | Argument Name | Value
Assets:    ID | Customer ID | Asset Type
Customers: ID | Name | Info | Etc...

We are kinda thinking about approach one or two. Somehow my gut feeling is
that number two wouldn't work but I can't rationalize it so I'm turning to
you guys to either prove me wrong or help me understand why I'm right.

Mike

P.S. As a sidenote, we're gonna start the system with only 150k customers
and it will have to grow tenfold so scaleability is a _must_ .