Table contains multiple names for same company

Question

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?

Thank you.

Answer 1

In this case I'd create a new table called CompanyAlias that will contain all possible aliases that you get for all companies. A possible structure would be:

CompanyAlias:

• AliasId - int - Primary Key - some generated id

• Alias - varchar(100) - the name of the alias

• CompanyID - int - Foreign Key - references the original table of companies (in case you don't have one, I believe you should create one table with all unique companies and any other details that you might get)

• any other column you'd like to have (description, creator..etc)

A query that will bring all the transactions for the main company of a given alias could look like:

declare @alias varchar(100);
set @alias = 'MicroS';

Select t.TransactionId, t.Alias, c.Company
from Transaction t
join CompanyAlias ca on ca.Alias = t.Alias
join Company c on c.CompanyId = ca.CompanyId
where t.Alias in
(
    Select ca.Alias
    from CompanyAlias ca
    where ca.CompanyId = 
    (
        Select ca.CompanyId
        from CompanyAlias ca
        where ca.Alias = @alias
    )
);

Where the tables would be:

• Transaction - contains monetary transactions (should contain company alias used by client)

• CompanyAlias - defined earlier

• Company - defined by you to have unique companies and their main details

PS: in case the SELECT statement is too bulky (written in notepad), I want to mention that I wanted to:

• get the CompanyId of the searched alias (the one that you specify in the search form)

• get all the aliases of the mentioned CompanyId

• get all the transactions of the mentioned aliases

Answer 2

I actually would create two tables:

Company and Company Alias

CREATE TABLE Company
(
    CompanyId INT IDENTITY PRIMARY KEY,
    FullName VARCHAR(250)
);

CREATE TABLE CompanyAlias
(
    CompanyAliasId INT IDENTITY PRIMARY KEY,
    CompanyId INT FOREIGN KEY REFERENCES Company(CompanyID),
    Alias VARCHAR(250)
);

Anywhere that you need to reference a company, you use the CompanyId from the Company table.

The advantage of this is that it gives you one unique Company record per company. The Aliases are merely attributes of this company, not the definition of the company itself.

---

Edit: After reading Marian's answer, I realized the assumption that there already was a company table compared to my assumption that there wasn't. So essentially, I agree with him, just under different assumptions.