How would I redesign my customers table?

[OLOT]

I'm trying to get a handle on normalizing my database to better manage a
very large customer table. (1 million records...) My databases main
function is managing direct mail campaigns and the call results from the
mailing. The mail department gives me the names with the following
information:

IdString,ListCode,ListString,name,address1,address2,city,state,zip,maildate
EXAMPLE: GA502-100358,GA502,100358,Norm Petersen,123 main street,apt
3,boston,MA,90210,02/28/2005

IdString = the code they customer uses when they call in, "account number",
primary key for customers table
ListCode = Foreign Key to the Lists table
ListString = a 6 digit number that is reset to 100001 each month, combined
with ListCode to create IdString and always keep it as a 6 digit number

I'd like to setup the customers table properly and effeciently so that I
have a solid foundation to build on when we start incorporating the ordering
process into this database. For reporting and maintenance I'm using Access
2002 "project" as a front end to SQL Server 2000 on a Windows Small Business
Server 2003 Premium server pc. For the actual sales reps handling the
incoming calls I'm using a web server program called "Resin" as a front end
to the same SQL Server back end.

I'm tracking call results for each list in the lists table, as well as stats
on each employee listed in a employees table. Most of the call result info
is stored in an orderinfo table that is essentially a 1-1 relationship with
the customers table. The "Resin" guru is working on normalizing that
portion of the project.

Any help would be appreciated.

 

[Tim Ferguson]

I'm trying to get a handle on normalizing my database to better manage
a very large customer table. (1 million records...) My databases
main function is managing direct mail campaigns and the call results
from the mailing.

Okay. At the moment we have (at least) the following entities:-

Targets
Campaigns
Mailings (this corresps to each envelope sent to each Target)
ReturnCalls (presum relates to Target+Campaign, or to each Mailing)

The mail department gives me the names with the
following information:

IdString,ListCode,ListString,name,address1,address2,city,state,zip,mail
date EXAMPLE: GA502-100358,GA502,100358,Norm Petersen,123 main
street,apt 3,boston,MA,90210,02/28/2005

IdString = the code they customer uses when they call in, "account
number", primary key for customers table
ListCode = Foreign Key to the Lists table
ListString = a 6 digit number that is reset to 100001 each month,
combined with ListCode to create IdString and always keep it as a 6
digit number

It's not quite clear how this relates to the situation given above. I.e:-

- Do you try to maintain links between this Norm Petersen and the
one you sent stuff to last month?

- Is the ListCode a reference to Campaigns as you described it above?

- Is the ListString actually the Target (customer) identifier?

- Do you care if you have two different addresses for the same
Norm Peterson? If you do, you'll have to do some parsing of this
input file and some deciding about which address to use. Oh --
and how do you know if it is the same one or a different one?

I'm tracking call results for each list in the lists table, as well as
stats on each employee listed in a employees table. Most of the call
result info is stored in an orderinfo table that is essentially a 1-1
relationship with the customers table.

Good news - every target a customer: bad news - nobody orders more than
once. If it's really one-to-one, why not just stick the stuff in one
table?

The "Resin" guru is working on
normalizing that portion of the project.

I don't think you can "normalise" part of a project. Either the thing is
correct or it isn't.

Hope it helps

Tim F

 

[OLOT]

thanks for the input. I've answered below inline...

Okay. At the moment we have (at least) the following entities:-

Targets
Campaigns
Mailings (this corresps to each envelope sent to each Target)
ReturnCalls (presum relates to Target+Campaign, or to each Mailing)

targets = potential customers
campaigns = each list that I mail
mailings = mailing details
returncalls = call center data

how about:

employees = sales reps

It's not quite clear how this relates to the situation given above. I.e:-

its not, other than this is how I currently dump it into my customers table

- Do you try to maintain links between this Norm Petersen and the
one you sent stuff to last month?

The mailroom processes all duplications and the goal is not to mail the same
person twice. (unless an entire campaign is rerun later on)

- Is the ListCode a reference to Campaigns as you described it above?
yes

- Is the ListString actually the Target (customer) identifier?

no, the IdString is the (customer) identifier, the ListString resets itself
to the same value each month. Therefore, the ListCode and ListString
combine to create the unique identifier.

- Do you care if you have two different addresses for the same
Norm Peterson? If you do, you'll have to do some parsing of this
input file and some deciding about which address to use. Oh --
and how do you know if it is the same one or a different one?

If a duplicate does slip thru, that (customer) will have to remain with
multiple entries because we track which campaign they are responding to...

Good news - every target a customer: bad news - nobody orders more than
once. If it's really one-to-one, why not just stick the stuff in one
table?

This is how it is now, and the table is getting difficult to manage

I don't think you can "normalise" part of a project. Either the thing is
correct or it isn't.

it isn't, and he's fixing it

 

[Tim Ferguson]

targets = potential customers
campaigns = each list that I mail
mailings = mailing details
returncalls = call center data

Quibble: "data" and "details" are not entities. You can have CallCentres,
or you can have ReturnCalls (this is what you mean, I guess); and I think
we already agree about Mailings.

employees = sales reps

You never mentioned anything about SalesReps, but yes, fine.

no, the IdString is the (customer) identifier,

It can't be. For a start it's a compound of two other attributes, and
therefore should not even exist in a table. To put it another way, having
an "intelligent key" is a Really Really Bad Idea. For next, it cannot
refer to a person (a Target or a PotentialCustomer) because part of it
gets reset every month; and because the other part of refers to a
Campaign, and that is going to change every time too.

I can see that it (or, rather, the combination of the two other keys)
would be a good Primary Key for the Mailings table, but it has nothing to
do with identifying a person.

and the table is getting difficult to manage

A table cannot get any harder to manage than when it started, as long as
it's designed right in the first place. A table of a million records is
no harder to manage than one of ten. The thing that makes tables bad is
having lots of fields, and you are not adding to them, surely? My guess
is that you are getting lost because you have lots of non-dependent non-
key attributes in this table because you have not decided whether it's a
table of Mailings, of people, of campaigns, or of return calls. You are
right: it cannot be all of them. They need a table each.

HTH


Tim F

 

[OLOT]

IdString,ListCode,ListString,name,address1,address2,city,state,zip,

It can't be. For a start it's a compound of two other attributes, and
therefore should not even exist in a table. To put it another way, having
an "intelligent key" is a Really Really Bad Idea. For next, it cannot
refer to a person (a Target or a PotentialCustomer) because part of it
gets reset every month; and because the other part of refers to a
Campaign, and that is going to change every time too.

What do you mean by "intelligent key"? Do you mean a field with more than
one meaning, a primary key with any meaning, or something else entirely?

I can see that it (or, rather, the combination of the two other keys)
would be a good Primary Key for the Mailings table, but it has nothing to
do with identifying a person.

Let me see if I understand then:

Targets primary key = autonumber (ie 1000587)
Campaigns primary key = autonumber (ie 1257)
Mailings primary key = (Campaigns PK + Targets PK)

When we mail out our mailpiece, the (promo code) would be "12571000587",
which would correctly identify the customer calling in and the campaign they
responded to.

A table cannot get any harder to manage than when it started, as long as
it's designed right in the first place. A table of a million records is
no harder to manage than one of ten. The thing that makes tables bad is
having lots of fields, and you are not adding to them, surely? My guess
is that you are getting lost because you have lots of non-dependent non-
key attributes in this table because you have not decided whether it's a
table of Mailings, of people, of campaigns, or of return calls. You are
right: it cannot be all of them. They need a table each.

bingo, and thats why I'm here trying to get it right.

 

[Tim Ferguson]

What do you mean by "intelligent key"? Do you mean a field with more
than one meaning, a primary key with any meaning, or something else
entirely?

An intelligent key is one which embodies more than one piece of
information in it -- it's a Bad Thing. I have to confess that I don't
really know why it's called "intelligent". What I do know is that it
makes all sorts of other joins and updates really hard and it is to Be
Avoided At All Costs. A compound key (i.e. one key made up of a
combination of two or more fields) serves the same purpose but means that
the fields themselves can still be used properly.

Targets primary key = autonumber (ie 1000587)
Campaigns primary key = autonumber (ie 1257)
Mailings primary key = (Campaigns PK + Targets PK)

Yup: I'd buy all that. If you already have identifiers for the people or
for the campaigns you (might be) able to use those rather than
autonumbers, but it comes essentially to the same thing.

When we mail out our mailpiece, the (promo code) would be
"12571000587", which would correctly identify the customer calling in
and the campaign they responded to.

Yes. You can use this on a form or a report or in a query just by
concatenating the value thus:

txtPromoCode.ControlSource = _
Format(CampaignNumber,"0000") & Format(CustomerID, "0000000")

or whatever.

bingo, and that's why I'm here trying to get it right.

I think that "kewl" is the word here..?

All the best


Tim F

 

[OLOT]

Targets primary key = autonumber (ie 1000587)

Yup: I'd buy all that. If you already have identifiers for the people or
for the campaigns you (might be) able to use those rather than
autonumbers, but it comes essentially to the same thing.

Campaigns primary key = text len5) "GU502","GU503",etc

would be ok if in current use?


Tim, thanks for all your help.

 

[Tim Ferguson]

Campaigns primary key = text len5) "GU502","GU503",etc

would be ok if in current use?

General rule for primary keys: they are
constant: not subject to change, it's a real pain to update PKs when
they are copied into related tables

unique: _no chance_ of a duplicate, ever.

known: no campaigns that don't have numbers, ever.

At first sight, that would seem to be okay; but only you know the
business. By the way, if the "GU" part is constant, you might prefer not
to bother to store it, but just keep the number and put the GU part back
on the forms, reports and letters etc. On the other hand, if the GU
actually means something, then perhaps this is an intelligent key too?

Tim, thanks for all your help.

Glad to: it's what we come here for!

All the best


Tim F