[RESOLVED] merging customer how is it done?

[salsa31]

hi,
i have 2 clients with the same name and phone number
now this client(s) are in a lot of places in the database
i need to know how can i merge the 2 clients
without losing any old data of the client name
by changing or merging it.
tnx for any help
salsa

[DEXWERX]

it's all custom based on the schema.
you have to update all the related records that reference the copy record, with the primary key of the first customer record.
Then delete the second copy of the customer.

[salsa31]

hey dex
so you suggest to copy and update all the data then delete it ?
no primary key for the customer

[DEXWERX]

I can only generalize without knowing the schema. Like how is it that the customer is all over the database without a primary key?

[dilettante]

This is why a normalized database design up front is so important.

Trying to normalize after the database has been populated often involves a lot of data cleansing effort to cope with things like variations in the way a name was typed in, typos or changes in things your phone numbers, etc.


But it sounds like you don't want to normalize the database now either. Instead you want to somehow "combine" different records.

If "Jack Black, 123-4567" has two unnormalized haircut records in what way would you "combine" them?

[salsa31]

I can only generalize without knowing the schema. Like how is it that the customer is all over the database without a primary key?

by fullname

[salsa31]

This is why a normalized database design up front is so important.

Trying to normalize after the database has been populated often involves a lot of data cleansing effort to cope with things like variations in the way a name was typed in, typos or changes in things your phone numbers, etc.


But it sounds like you don't want to normalize the database now either. Instead you want to somehow "combine" different records.

If "Jack Black, 123-4567" has two unnormalized haircut records in what way would you "combine" them?

jack black 944555
jack black 944555
combine it in to 1 and then updte all records based on the changed

[ColinE66]

It has nothing to do with normalization - that's about how your data is modelled (i.e. the tables you created and how inter-relate). You can have a fully normalised database design and still encounter this problem, because it has to do with how you have defined your primary keys. Relying on uniqueness of name is obviously very bad. Something like a National Insurance Number (or whatever that is, in Spain) is very good.


btw, Salsa, I believe you have been warned about the potential for this happening in the past (more than once). Can't say I'm over-flowing with sympathy right now!

[Spooman]

Salsa

DEX seems to be onto it, but just to be sure ..

When you say ..

i have 2 clients with the same name and phone number
now this client(s) are in a lot of places in the database
i need to know how can i merge the 2 clients
without losing any old data of the client name
by changing or merging it.

.. it sparks a couple of questions :

1. is it really just one client that appears 2 times in, say Table-1 Clients?
2. if so, what other fields appear in Table-1?
3. how many other tables do you have where this "one" client appears .. what are table names?

Correct me where I went wrong.

EDIT-1:

FWIW, posts #6, 7 and 8 came in while I was composing.,

Spoo

[salsa31]

hey spoo
well i just found a solution
i will copy same data change the name and then update in all the tables the client is with the older name and the new
name.

[ColinE66]

Salsa, a solution would be making sure it cannot happen again not fixing the data afterwards!

[Spooman]

hey spoo
well i just found a solution
i will copy same data change the name and then update in all the tables the client is with the older name and the new
name.

Glad to hear you found a solution.

Spoo

[SamOscarBrown]

FWIW, posts #6, 7 and 8 came in while I was composing.,

You must type (or think) slowly!

[SamOscarBrown]

@salsa

What are you doing to prevent something like this in the future? Let's just say someone (else) types in a fullname and his/her telephone number for a hair dye appointment. He/she comes in, gets it done, and pays. (I am sure someone then records that transaction.) But then, the next day, the same dude/dudette shows up and wants a haircut. A different employee, not knowing if Jack/Jane Black is already a customer, types in his/her fullname and telephone number. Then cuts the hair, and completes the update of this 'new' customer. You will be right back where you were at the beginning of this Thread. SO, what ARE you doing to prevent something like this in the future? (Remember, hair 'clerks' probably don't have IQ's like Einstein!)

[Spooman]

You must type (or think) slowly!

Har har har

But give me a break .. those 3 posts occurred within a 4 minute time stretch.
I thought it was a VBForums rule to take at least 5 minutes to compose a reply.

Spoo

[ColinE66]

Har har har

But give me a break .. those 3 posts occurred within a 4 minute time stretch.
I thought it was a VBForums rule to take at least 5 minutes to compose a reply.

Spoo

Nah. You are thinking of the 1 minute rule for composing a question. Such a tight deadline that it can only be achieved by typing in text speak and omitting as much information as possible.

[Spooman]

D'oh.