May 17, 2007 at 7:05 am
Actually I posted this in 7,2000 when I meant to put it in 2005 Strategies for better view.
link to re-post: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=361&messageid=366930
We're going to be re-working our back-end system in which we store our customer and their historical information.
The flaw that we've had for years is that the data is easily corrupted due to our processes. We used Telephone numbers as the Primary ID and while for our industry it works "good enough" at the end of that day that just simply doesn't cut it.
Thus, I'm looking to try to strategize a sytem in which we can reasonably assume that an entry is unique across our company.
We have two sections (Telemarketing and Direct Mail).
So far, they have always been seperate, so one of the pieces of this project is to look into ways to tie them together.
For our telemarketing clients it's reasonable to assume we'll have the following:
Phone Number, FirstName,LastName,Address,Zip
The only "issue" with these we have to solve is linking up Husbands and Wives at home,etc. Thus my plan was to simply add a column for CustomerID in and just have that be my primary key and allow business rules to handle the rest of the logic required.
However, when I pull something like integrating direct mailers into this, I'm going to end up losing the phone number, but having the rest of the information.
In my mind, the logic still doesn't change, I just need to do a bit more work in the business layer in order to make sure that I'm staying unique. However my concerns are when people mis-key information and/or shorten names,etc. IE Bob=Robert or Jimmy=Jimy which would obviously fail updates that link on exact entries.
I'm sure many companies have to work on resolving this exact type of problem and I'm wondering what kind of experiences others have had in solving this issue.
TIA
May 18, 2007 at 8:05 am
Terry--
I'd give serious consideration to using a GUID as your Unique Identifier (customer_id) and not try to use information that is changeable such as phone numbers or names. The rub might be in integrating the direct mailing.
--Jim
May 18, 2007 at 8:11 am
That's exactly what I had thought about doing since it could persist throughout systems.
The issue I have though is how atypically people handle integrating the various types of records.
I'm sure it's going to be interesting to say the least.
October 1, 2007 at 9:58 pm
GUID or int, use some type of surrogate key.
Expect that data will not be clean, and I'd build routines to make it easy to let people "link up" or correct data.
Or break links when you find out a phone number is being reused by someone new.
If you design with the expectation that there will be data quality issues, you'll be in better shape.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply