Data Normalization - Customer ID's

[Minitman]

Greetings,

I am not sure which newsgroup to ask this question. Any directions
would be most appreciated.

I have a list of about 5000 customers over a 30 year period. I am
trying to give each customer a unique ID. Some of these customers
have moved a few times, some of them have gotten married a few times
(we deal with the wives primarily), some have multiple service
addresses.

This question has plagued me for the last 30 years!!!! Any
suggestions would be most welcome.

TIA

-Minitman

 

[Frank Kabel]

Hi
probably better asked in a database related newsgroup. What you're
asking for is quite typical for storing customer information and
contact data in a normalised way. Best way to start would be to get a
book concentrating on data normalisations.

Some general ideas (definetely not fully normalised but to get you
started):
1. Table: (MasteData)
- Customer ID (unique key)
- Surname
- Firstname
- Start of service
- Status
- Date/Timestamp
Note: Not sure if you need historised data for any changes in this
table. e.g. do you want to know what surname the customer had 20 years
ago. if yes I would probably historise this table (using the timestamp
together with the unique ID to get the current valid information. You
could use this also to already enter name changes or other changes in
advance making them valid at a specific date/time)

2. Table (Contact Data)
- Contact-ID (unique)
- Contact Type (e.g. telephone, address, email, etc.)
- Status
- Date/Timestamp´
Note: now create sub-tables which store the relevant contact data for
such things like telephone, addresses, email, etc. another option would
be to add these fields to this table making the data fiedls partly
optional.


Also I would not do this within Excel but in a database (e.g. MS
Access). For MS Access I think MS already provided some templates for
this kind of data

 

[Minitman]

Thanks Frank,

I was afraid it was not a simple answer. Do you know the names of any
good books on the subject?

-Minitman

 

[Frank Kabel]

Hi
the problem is I know only some good German books - probably of no help
for you
I would go for example to the Access newsgroup and ask for a good
intructionary book on normalisation