0

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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.