Multiple entries

[moori]

Hello everyone,

today I have a design problem: I have customers in my database who have more than one customer number, but every other information (Name, Categories,...) is the same - please don´t ask me why they need more than one number, I don´t get it myself! So for some customers I have multiple entries, just differing in the customer number, which is my primary key.
Now when someone would update a customer´s information, is it possible to update this info automatically for all of his customer numbers?
Or was it stupid to use the customer number as primary key in the first place?

Any opinion is appreciated,
moori

 

[jdraw]

If you are a business, then your Customer list is critical. It should be up to date and it should be maintained.

How did multiple customer numbers get assigned? Is there a weakness in your processes?
What do you plan to do now?

 

[moori]

No, the multiple customer numbers were assigned intentionally for reasons I don´t really get, but my boss insists that it must be so. When creating the database, I made a separate entry for each customer number rather than for every customer name, because the number is unique...

 

[spikepl]

It seems to me that the sensible thing to do would be to determine what the various entries with the same customer name but different customer numbers share, and what they do not share. Prior to making any code that can do sweeping updates, that is.

 

[moori]

They share every information that is relevant for the database - the name, an agent, and categories for different product families (that determine discounts).
They do not share the customer number, that´s all.
So it would be good when updating the categories for one number, all other numbers of the same customer would be updated, too

 

[spikepl]

If data is shared 100% then your db is not normalized, and that is a design flaw. It would be better to patch the design flaw than create code for ongoing patching of the data. If there is some other significance to the fact that the same customer data appear under multiple customer numbers, then it would be pertinent to find out what it is.

Alternatively, ask your boss to sign off on a sweeping update before you do it. That is CYA in action. For your updating you need to identify all the records - by whatever means - that contain info for the same customer and then update those.

 

[moori]

I was afraid that would be the answer ;-)
Yes, there is some significance in "real life" for the multiple numbers, but it doesn´t play any role in the database. Hm, I´ll work on this one.... thanks for your answers!!!

 

[jdraw]

Just thinking as I type --
Could you add another field in the table "LogicallyDeleted" or something obvious.
You could put a Yes in this Boolean field.
In your queries against the table you would ignore the record(s) that have LogicallyDeleted -1/True/Yes. You would have to have some sort of policy and communication to the developers of that special condition.

My thinking is, if the boss wants the duplicated Customer info which violates Normalization (and common sense), then you could use 1 of the records from this point on and keep the other records "available in the table, but logically removed from processes" using the boolean.

You might even create a special query/form/report to highlight this link between "the active company record and those that have been logically deleted".

It's a work around at best, and it should be obvious why "the boss' request has been accommodated" in documentation/ forms etc so that the source of this "glitch" is recognized now and in future.

It wouldn't be the first system with a "strange condition embedded". There may be other mechanisms to appease the Boss without losing total control, as well as the CYA aspect mentioned by spikepl.

Good luck with whatever is decided.

 

[gemma-the-husky]

I have seen this sort of problem even if not exactly the same.

Typically a big customer might have several similar named subsidiaries, or even branches of the same company, all of which need separate sales accounts. It can then be very confusing when they try to select the one they need with a combo-box, say.

if they take this to the extreme of having 2 (or more) identical names (how DO they distinguish them?) it is just practically very awkward

if this is YOUR system, maybe YOU need an extra field (just a comment) to enable you to distinguish the purpose of the separate accounts.

 

[moori]

Thanks for your new input! The more I read and think about it, the more obvious it becomes that I should delete the duplicates. There is no way to keep all data accessible for my users AND sustain data integrity.
I could try a message box on update: "Please be sure to update this information for all of the customer´s accounts". This would certainly avoid all chaos :-D