Advice on building a table

[PRMiller]

I am creating a database that will outline call routing for thousands of toll-free numbers. My data looks like this:

Policy #  Account Name  Toll Free Number
11111     ABC Company   800-111-2222
22222     DEF Corp      805-111-3333

Of course, there are about 40 additional columns of data relevant to each entry in the table. I would normally build out like this:

numRecord_pk  numAccount_fk  varTFNNumber
1             986            8001112222
2             1020           8051113333

Where 986 and 1020 are foreign keys to a client table. I don't believe that's going to work in this situation. Many clients have multiple telephone numbers, as follows:

Policy #  Account Name          Toll Free Number
11111     ABC Company           800-111-2222
11111     ABC Company Retirees  800-545-1111
11111     ABC Company HR        800-222-9999
22222     DEF Corp              805-111-3333
22222     DEF Corp (MI)         888-444-6060

It is not enough to be able to identify the client -- I also need to know the function. My users will want to know which one of ABC Company's toll-free numbers is used for retirees, and which one is the general number.

Here's my current thinking, and let me know if I'm way off base here:

Import a list of policy numbers and client names. Append an identifier for each, as follows:

numAccount_pk  numPolicyNum  varAccountName          
100            11111         ABC Company
101            11111         ABC Company Retirees
102            11111         ABC Company HR
103            22222         DEF Corp
104            22222         DEF Corp (MI)

Append THIS identifier to the toll-free number table. DTS packages would update production tables from source files for both the client table and the toll-free table.

Thoughts on this? There will be approximately 10,000 records, couldn't tell you the size of each yet. Is this the best way to do this? Or should I just load the data as ugly as it is, not worrying about attempts at normalization?

Thanks,
Paul

 

[Jasen]

I don't see what's wrong with just doing it like this:

PK        PolicyNum  AccountName           Toll Free Number
101       11111      ABC Company           800-111-2222
102       11111      ABC Company Retirees  800-545-1111
103       11111      ABC Company HR        800-222-9999
104       22222      DEF Corp              805-111-3333
105       22222      DEF Corp (MI)         888-444-6060

 

[rmnastor]

normalization is a good thing but sometimes there are instances where hibrids of DBMS models is more appropriate... especially when speed has to be considered...

 

[PRMiller]

Thanks for your replies. I think you're right, I'll have to do it this way. I guess I just hate the fact that I can't reduce the primary table to all numeric values!