Handling variations of a customers name on an order

Question

Handling variations of a customers name on an order

JohnMarks

Ten Centuries

Points: 1253
More actions
May 5, 2011 at 6:53 am

#250382

I am looking for opinions on how people model the entities which may have to store a number of different customer names that might be tied to an order. For example a customer might use just 1 name for placing the order but ship to another name or pay with a credit card with a different name with which they used to place the order. Our current model has customer table which a first name, middle and last name fields and an address table which has the standard address fields. With the separate address table we can store multiple addresses (i.e. shipping billing etc) for a customer easily enough but I am having trouble deciding on how best to store the different names that may be associated with an order.
My current thought is to have name fields in tables that store the shipping info or billing info as well as keeping a name field in the customer table but in the cases where the name is the same value that seems like it would cause a bit of duplicate data. Alternatively I could have an entity which just stores customer names and then tie that to the various other entities such as customer, order, billing, shipping but that seems overkill.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

John I'm not saying this is the right way to do it, this just happens to be how I thought it might work;

how about creating a table CustomerAlias, and it basically would have the customerId field and a couple of description columns;

whenever a search is performed in the database, the original table UNION that alias table would be used to perform the search to return a customerId /Info based on the description?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 2

This is one case where I think a bit of potential duplication may be OK.

Your other suggestion... have a table of customer names then link to that for billing, shipping etc has a potential problem when it comes to updating customer names. e.g.

Suppose the customer just has one name to start with, and you link to this name for both Billing and Shipping.

If you change this name, how can you be sure that the change should apply to both Billing and Shipping. If it only applies to Billing, then you then have to go and create another customer name and link that in to the Shipping name.

Edit. Just corrected some typos.

JohnMarks Ten Centuries Points: 1253 More actions · Answer 3

Lowell (5/5/2011)
John I'm not saying this is the right way to do it, this just happens to be how I thought it might work;
how about creating a table CustomerAlias, and it basically would have the customerId field and a couple of description columns;
whenever a search is performed in the database, the original table UNION that alias table would be used to perform the search to return a customerId /Info based on the description?

CustomerAlias entity is not a bad idea. I'll mull that one over. Thanks for the suggestion.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

JohnMarks (5/5/2011)
I am looking for opinions on how people model the entities which may have to store a number of different customer names that might be tied to an order. For example a customer might use just 1 name for placing the order but ship to another name or pay with a credit card with a different name with which they used to place the order. Our current model has customer table which a first name, middle and last name fields and an address table which has the standard address fields. With the separate address table we can store multiple addresses (i.e. shipping billing etc) for a customer easily enough but I am having trouble deciding on how best to store the different names that may be associated with an order.
My current thought is to have name fields in tables that store the shipping info or billing info as well as keeping a name field in the customer table but in the cases where the name is the same value that seems like it would cause a bit of duplicate data. Alternatively I could have an entity which just stores customer names and then tie that to the various other entities such as customer, order, billing, shipping but that seems overkill.

Is this an OLTP database?

If yes, I will start by modeling different entities for CUSTOMER (C), CUSTOMER_SHIPPING_ADDRESS (CSA) and CUSTOMER_PAYMENT_METHOD (CPM), I will most probably having a one-to-many relationship between C<=>>CSA and C<==>>CPM

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.