A single company can be calle numours names (1 Viewer)

[KeithG]

I have a db at work where a user will look up a company name and retrieve the CustomerNumber and PayCode (these two values change monthly) to input the numbers into our mainframe program. The users retrieve the company name from documents they are keying from so naturally different clients give different versions of a companies name. As a result the user will look up a company name and will get no results returned because the name is not formatted correctly. I inheritted this db and I am planning to redesign it. One more thing to note is that a single Company can have multiple CustomerNumbers and PayCode broke down by state. So to recap One company can have one Offical Name but many aliases. And One Company can have many CustumerNumbers and PayCodes. Below is what I have brianstormed and would like any input or suggestions. One thing I am concerned about is the State attribute. Some companies have a single custnumber and paycode for ever state, while others can have a custnumb and paycode for every state. What do I populate in the state field for companies with a single custnumb and paycode? You might be thinking I could use ALL meaning ever state but what happens if a company has a different custnumb for Wisconsin but every other state is tied to a single custnumb and paycode. Please let me know if you have any suggestions or see any flaws in my design.


tblCompany
CompID
OfficalName
Street
City
State
Zip


tblAlias
CompID
Alias

tblCompanyCodes
CompID
CustomerNumber
PayCode
State

 

[gemma-the-husky]

i thnik you'll struggle with the aliases

best way is to construct a soundex of the real name and either match the soundex, or search a partial string.

 

[The_Doc_Man]

Keith, I would say that first you need to gather up the folks who allowed these aliases to exist and give them a group lashing with wet noodles.

Then you need to eliminate the aliases in some way. If there is NO common element that allows you to identify that you have an alias, though, you will have nightmares over this one. Heck, a former employer had a nightmare over the name they chose for re-incorporation after a merger. Turned out the name was already taken. After discussing the matter with the holder of the earlier version of the name, they settled on our company using the name but adding "of {state-name}". So if it happened that your business contacts include computer management services and biochemical analysis, you would have come across two different companies with the same name - but one would have a state name added to it.

It's all moot now - I no longer work for them and they got bought out by a company with a totally different name. But the point of this little anecdote is that even a Soundex spelling won't help sometimes. Nor a partial match.

If you can't match zip codes or phone numbers or corporate addresses or corporate taxpayer IDs, you are going to have a royal pain with this problem.

On second thought, that "wet noodle" session sounds too tame for the trouble your predecessors have caused you. Check the local underground weekly rag to see those ads for "Lady Latex and her Talking Paddles." It won't fix the problem but at least SOME justice will be handed out.

 

[Dreamweaver]

I use a form of auto correct in one of my programs which deals with chart data from around the world.

Haven't got much time at the min but it uses a second checking table with incorrect names that are assigned to the correct one

This is the system I built for working with it all
http://www.databasedreams.co.uk/chart_tracker/satellite_importer_gallery.html