I have a table of monetary transactions that has a field for who the money was paid to or received from. Transactions are recorded using info from paper invoices, paper reciepts, internet reciepts, or whatever other type of reciept is turned in or available.
Some (many?) companies use different names for themselves in different places. The name that shows up on a credit card statement is likely to be different (slightly or greatly) for the name they print on their POS paper receipts, etc.. The people entering the data can't always be relied on to remember all the naming variations on all the different companies.
So in our db, one company may have transaction records under several different names.
I'd like to have a form where I can enter known naming variations for companies and have the db keep track of multiple names that are really the same company, so later I can query and get far more trustworthy data returned. Also, I need to be able to query using any of a company's "AKA's" and bring back all their transactions no matter which name was used when the transactions were entered.
The problem is that I'm not sure about the table structure to use. There could be 3 or more AKA's for one company. How can I do this?