design issue

[sweetleaf]

Hi there ,

this is more of a good database design issue rather than it being an oracle specific question:

if my company sells video games online and in stores
to customers but i want to keep track of these customers when they call in for tech support or fill out online warranties or customer support forms - how can i come up with a primary key in the customer table?
I don't want to use an account number as a customer won't remember their own account number when they call in to log an issue, yet i need a unique constraint. i was thinking about a composite key of First Name, Last Name and Phone Number but obviously this lend itself to many potential problems: ie.changed phone number or someone filling out a support ticket as John Smith one time and Jonothan Smith the next...

With companies which are "subscriber" based this is not a problem as the customers are sent regular bills - but when the customers are "come and go" like at McDonald's or Walmart yet they need to be tracked - this is where i am coming up with problems for a ound backend design...

Any and all help will be greatly appreciated

 

[jee]

Your composite key won't be very efficient because it is long, plus the problems you mentioned. One suggestion: Use an artificial key (in Oracle, created by an Oracle sequence generator).

When a customer phones in or comes to store and gives his/her name, the operator can lookup on that - and the application can list name/address/phone. If there are duplicates, operator can differentiate based on address/phone.

 

[sweetleaf]

Thanks Jee,

This addresses one half of the solution: phone ins.
But what about when a customer on seperate occassions fills out online customer support forms/warranties on te website -how will the system be able to guarantee that for each form filled out that was the same customer? (customer may fill out "J Smith" on one form and "Johnothan Smith" on another).
The artificial key, although a great suggestion, does not resolve this situation.


Thanks

 

[rcurva]

An artificial key for me is good enough. In this world of electronic information, we always carry account nos. to pay bills, to view our accounts, to log on a site, to phone in our balances. So, it is not within your control any more if your customer forgot his account no., cases like this will have to be handled manually by reissuing new account no. for the customer.

I understand that you only want to give good service to your customers. Issuing artificial key will save you from the troubles of generating duplicate customer information, thus saving you the quality time of cleaning up your DB, and ultimately, giving optimum service to your customers. Can you imagine how many John Smith living in this earth right now?

Hope I gave you an idea.


Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[johpje]

I also would recommend the use of an artificial key. For the names issue there are several solutions. Depending on how you are planning to use this information.
The basic thought behind most of 'em is that a customer must always fill in his whole surname.
Here are 2 examples on how i have seen it done:

There are 2 (or more) fields for name. first and surname. The only real disadvantage of this is that you need to check more than one field if searching for a full name. But this way it's easy to cover inputs of J instead of the complete name

Another solution is to make one name field with a specified format. ex: Smith, Johnathan
This should clears virtualy every problem of searching on a name. Although you need to use some more functions if you want only a part of the full name.

I hope this is of any help to you.
Johpje

 

[Triton46]

I would also add that you mentioned are skating on two different design philosophies for your customer table.

Changes in Address, Name, Phone:

In the DW industry, the customer table is the most dynamic. People can change there information (even names) anytime.

One philosophy is to update the customer information (update address, update phone, etc) and the customer keeps the same surrogate key (sequence number)

Another philosophy is to have multiple accounts. (ie John Smith at 123 West Street is customer 1 and John Smith who moved to 124 East Street is customer 2). The same customer now has two customer numbers, however you can track how many videos John rented at his old and new location.

In the corporate world the latter is how you want to track it. If Home Depot moves a store across town in 2001 and you update there address, then you now show all there sales prior to 2001 at the new address, but how they were never there (does that make sense?).

For your purposes, you can add a flag that distinquishes new addresses from old/prior addresses (or phone or name or etc).