Names of businesses keyed differently by different people

Question

I have this table

tblStore

with these fields

storeID (autonumber)  
storeName  
locationOrBranch

and this table

tblPurchased

with these fields

purchasedID  
storeID (foreign key)  
itemDesc

In the case of stores that have more than one location, there is a problem when two people inadvertently key the same store location differently. For example, take Harrisburg Chevron. On some of its receipts it calls itself Harrisburg Chevron, some just say Chevron at the top, and under that, Harrisburg. One person may key it into tblStore as storeName Chevron, locationoOrBranch Harrisburg. Person2 may key it as storeName Harrisburg Chevron, locationOrBranch Harrisburg. What makes this bad is that the business's name is Harrisburg Chevron. It seems hard to make a rule (that would understandably cover all future opportunities for this error) to prevent people from doing this in the future.

Question 1) I'm thinking as the instances are found, an update query to change all records from one way to the other is the best way to fix it. Is this right?

Questions 2) What would be the best way to have originally set up the db to have avoided this?

Question 3) What can I do to make future after-the-fact corrections easier when this happens?

Thanks.

edit: I do understand that better business practices are the ideal prevention, but for question 2 I'm looking for any tips or tricks that people use that could help. And question 1 and 3 are important to me too.

Answer 1

This is not a database design issue.

This is an issue with the processes around using the database design.

The real question I have is why are users entering in stores ad-hoc? I can think of scenarios, but without knowing your situation it is hard to guess.

The normal solution is that the tblStore table is a lookup table only. Normally users only have access to stores that have already been entered.

Then there is a controlled process to maintain the tblStore table in a consistent manner. Only a few users would have access to this process.

Of course as I alluded to above this is not always possible, so you may need a different solution.

UPDATE:

Question #1: An update script is the best approach. The best way to do this is to have a copy of the database if possible, or a close copy if not, and test the script against this data. Once you have ensured that the script runs correctly, then you can run it against the real data.

If you have transactional integrity you should use that. Use "begin" before running the script and if the number of records is what you expect, and any other tests you devise (perhaps also scripted), then you can "commit"

Do not type in SQL against a live DB.

Question #3: I suggest your first line of attack is to create processes around the creation of new stores, but this may not be wiuthin your ambit.

The second is possibly to get proactive and identify and enter new stores (if this is the problem) before the users in the field need to do so. I don't know if this works inside your scenario.

Lastly if you had a script that merged "store1" into "store2" you can standardise on that as a way of reducing time and errors. You could even possibly build that into an admin only screen that automated merging stores.

That is all I can think of off the top of my head.