Storing information about office locations for international companies

Question

I have a table in MS Access containing a list of about 500 companies and some information about them. I am trying to create another table that contains information about the office locations of these companies. I am think to populate this table with the following fields:

Country, Company 1, Company 2.... all the way to Company 500.

The Country field will contain all the countries in the world. The other 500 fields will contain True/False values for whether the company has an office in the country in question.

Needless to say, this will be a huge table containing true/false values, furthermore it will be difficult to populate and enter data accurately for this table. Does anyone know of a more "correct" and data efficient way to store this information and query it easily? The objective is to be able to summon a list of all the countries where each company has an office location easily and efficiently. Or to start from a country and come up with a list of all companies present in that country.

Answer 1

To normalize this properly, you need what is sometimes called a cross-reference or link table, to avoid making that country table so wide.

For example (this is for demonstration purposes only):

company
-------
companyid
companyname

country
-------
countryid
countryname
countrycode

country_company
---------------
countryid
companyid

With this structure, when you add a new company, you add it to the company table, and then add a reference to it in the link table.

So if you had 100 countries, you would have several hundred rows in this table.

I can assure you that querying this will be far more efficient than a very wide table.

When you want to query the data, you'll need to write an additional JOIN, but this makes managing the data so much easier, and less error-prone.

Notice that I've added a countrycode column, which I would use for a two- or three-digit ISO standard (e.g. "USA", "CAN").

This means you could even use the code as a foreign key instead of the countryid.