How can I organize my database?

[Giovanni]

How will I be able to organize the database in order to put more than one
value in one field (such as countries and regions)?
The problem is that I need to organize the database in order to have the
chance to put more countries and more regions for each agency at the same
time.
Could you please help me?

 

[Jeff Boyce]

Giovanni

I'm not sure I understand what you are trying to do...

Are you asking how you can put multiple values into a single record's field
(perhaps something like "France; Germany; Italy; ..." in a single field)?
If so, please reconsider. This reflects a one-to-many relationship between
your record and the values you are trying to stuff into the single field --
in a relational database (Access), you don't want to do this.

While it is possible to do, it will only cause both you and Access
headaches, as you will need to build work-arounds to handle what Access
could easily manage if the data were more fully normalized.

Please explain a bit more about:

have the
chance to put more countries and more regions for each agency at the same
time.

 

[Giovanni]

Dear Jeff,



I am sorry if I wasn't clear enough, I will try to explain it better.



To give a bit of background, I am doing this for a Humanitarian
Organisation. We need information on other humanitarian organisations' head
quarters, and the various offices under the HQ's juristiction.



I am trying to design a database in order to store information on as many
humanitarian organisations presently with HQ's in Europe as possible, in
particular, I have divided the data in 2 different category such as:



Organisation

- Main Address, Name of the Organisation, Organisation E-mail, Website,
Organisation's telephone number, Organisation's mobile (if any) and a few
more generic categories that apply to the organisation as a whole. I won't
have any problem to do that.



The trouble for my point of view starts when I have to store different kinds
of data under the main organisation file; such as:



Regional Branches

- Regions of operations for each agency. Depending on the organisation, this
could be 1 - 20 different ones.



then:



Country Branches

Countries of operation under each regional office. Depending on the
organisation, number of country offices can vary greatly in number.



then:

District Branches

Districts of operation under each country office. Depending on the
organisation, number of district offices can vary in number.



then possibly:

Partner Organisations

Big organisations normally team up with local grass-root organisations.
Partner organisations will be logged the same as a main organisation record.



As you can see the second data requires more attention in planning the
database, and because I cannot put multiple values into a single record's
field, I would appreciate your advise. I would like to ask you if you can
give me any suggestions in order to plan this database in a proper way.

 

[Jeff Boyce]

Giovanni

From your description, I'm visualizing the following:

You are interested in organizations.

Organizations can have zero-to-many "sub-organizations" (e.g., Division,
....)

You are interested in the organizations' (and sub-organizations') office(s)
(?is this synonymous with "physical location"?).

One organization (sub-organization) can have one-to-many offices.

Offices can be categorized as "belonging" to different levels within an
organization.

What's emerging to me is a table of organizations (and sub-organizations,
and partners and ...), to uniquely describe each.

A second table would hold all the relationships (e.g., organization #33 is a
division of organization #2; organization #17 is a partner of Org#3;
Org#23 is a Country Branch of Org#12...).

To fill in this table, a third table would hold a simple list of valid
relationships (e.g., division of, partner of, Country Branch of).

If you would want to be able to look "up and down" this chain, you could
also add a field in the second table that explains the "reverse"
relationship, and add necessary valid relationships to the third table
(e.g., Org#1 is a "head office" of Org#7...)

Just one person's opinion...

Jeff Boyce
<Access MVP>