I am interested in improving a database of businesses which attempts
to do the following:
- Incorporate office locations for businesses
- Incorporate the geographic regions served by the businesses (which
may be broader)

The businesses enter their own information through an online
interface. Thus, it is cumbersome to have a system which requires the
businesses to enter dozens or hundreds of different states or nations
for the regions they serve, but instead to more easily designate the
scope of their services.

The information will ideally be searchable by both office location,
and by region served. That is, if somebody wants to find a business
that has offices in Michigan, they should be able to search by office
location. If they want a business that performs services in Mexico,
they should be able to search by region served.

The goal is to have a system that is easy for the businesses to use
and update, but also allows for relatively simple database management
and searching.

What is the optimal manner of structuring the data, for ease of entry
of data, management of the database, and ease of searching? (If there
is more than one good answer, you may wish to highlight the key
differences between the approaches, and indicate which you believe is
best - but you need only detail the approach you believe is best.)
Examples are helpful.

If you do not have direct experience with databases, it would be best
to leave this question to another researcher.

---

Request for Question Clarification by hammer-ga on 02 Apr 2003 11:52 PST

Expertlaw,

Some questions:

1. You mention searching by state. I assume this is USA states. How
much detail do you need for other countries? Is it sufficient for the
company to say they have offices in Germany, or do you want to list
the Hamburg, Dusseldorf and Berlin separately?

2. As far as your needs are concerned, does the USA have regions other
than states? Southwest, Northeast, Great Lakes, etc...

- Hammer

---

Clarification of Question by expertlaw-ga on 02 Apr 2003 14:28 PST

With regard to your questions:

1. Searching would be necessary by U.S. state and Canadian province.
It might also be nice to search by Australian province. Otherwise by
nation.
 
2. It might be nice to be able to break the U.S. into regions, but it
is not absolutely necessary.

General Notes:
1. There is a balance you need to maintain. The easier things are for
your  users, the more work needs to be done by the developer. The
methods suggested below can be tweaked, depending on where you choose
to set the sliding scale between your burden (of time and expense) and
that of your users.
2. Based on the priorities laid out in your question and your RFC, I
am suggesting a method that will keep your data relatively simple,
while still offering you a lot of choices for how to present the
information to both groups of your users.
3. I assumed that you are using a relational database. I also assume
that you have a table in your database called tblBusinesses which
stores, among other things, a unique id for each business
(BusinessID).

--- The Back End - Database Tables ---
I recommend creating a table to store your geographic areas. Let's
call it tblRegions. tblRegions can have the following fields:
RegionID - Autonumber - Unique ID
RegionName - Text - The name of the region, i.e. Michigan, France...
RegionGroupID - Number(Long Integer) - See below for details

The above table will allow you to define and redefine the regions that
you are interested in as you see fit. If you want to list all the
counties in Australia and all the individual boroughs of New York, but
only list the country for Germany, then you can do that. The
RegionGroupID allows you to tie Regions together. For example, you can
list Vermont, New Hampshire and Maine as separate states, but also tie
them together for search purposes as US Northeast. The records in
tblRegions would look like this:

RegionID     RegionName     RegionGroupID
-----------------------------------------
1            US Northeast   
2            Vermont        1
3            New Hampshire  1
4            Maine          1

You can cascade RegionGroups to develop whatever relationships you
need. For example, if you want US Northeast to also be found for
searches on United States, then:

RegionID     RegionName     RegionGroupID
-----------------------------------------
1            US Northeast   5  
2            Vermont        1
3            New Hampshire  1
4            Maine          1
5            United States

Depending on how you write your queries, Vermont, New Hampshire and
Main can also appear in searches for United States.

Once you have the data entry done on this, it's all in how you write
your queries for what you get back when. It also makes it very easy to
do things like move countries out of Europe and into the European
Union.

OK, now that you have tblRegions, you need to tie the Regions to the
Businesses. We'll do that using a junction table. A junction table is
a small table used to avoid many-to-many relationships. If you want to
be bored by why that would be a problem, please post an RFC and I'll
be glad to elaborate :>.
We'll call the junction table tblBusinessRegions. This table will
contain one record for each Region a Business inhabits. It has the
following fields:
BusinessRegionID - Autonumber - Unique ID
BusinessID - Number(Long) - BusinessID from tblBusinesses
RegionID - Number(Long) - RegionID from tblRegions
Type - Text - Office or Service Area

If a Business has offices in Maine and Vermont, but serves the entire
US Northeast, then its records in tblBusinessRegions would look like
this:

BusinessRegionID  BusinessID  RegionID  Type
----------------------------------------------------
1                 1           4         Office
2                 1           2         Office
3                 1           1         Service Area

Depending on your needs, you may want to have separate junction tables
for Office and Service Area. For efficiency and flexibility, you may
also want to create a small lookup table for the Types.


--- The Front End - User Forms ---
At this point, you have a lot of choices for how you present this
information to your users. It's difficult for me to make
recommendations without more information, but here are some
suggestions:

On the website where your businesses update their info, you can offer
them a choice of Quick Entry or Maximize Hits. Quick Entry takes them
to a broad image map or set of lists where they are offered detailed
choices for their own country, but only the broad choices for other
countries. This will cause them to appear in less searches, but they
get out quicker. Maximize Hits presents drilldowns with lots of
specific choices where they can select all 60 office locations and 20
additional service areas. They do more work, but they hit in many more
searches, and they elected to do the extra work.

The Searching users can be offered detailed drilldowns to perform
their searches, enabling them to easily change search scope to include
RegionGroups searches, etc.

Wrapping Up:
Without a more detailed understanding of your requirements, budget and
priorities, I can't lay out a complete plan for you, but I hope this
gives you an idea of how you can set up such a thing. There is a lot
of work here for the person who needs to build the forms and queries
that make this sort of system do its job. I recommend you get their
input before making a decision. Please request clarification if you
need me to elaborate on any particular point.

Best regards,

- Hammer

---

Clarification of Answer by hammer-ga on 02 Apr 2003 17:47 PST

I should also mention that the light data entry route does not need to
result in a large loss of search hits. Depending on your application,
a Business User can simply select US Northeast and automatically
appear if the Search User searches for Vermont, New Hampshire or
Maine. The RegionGroups cascade can work in both directions.

- Hammer