Design question

  • Thread starter Thread starter Lars Brownies
  • Start date Start date
L

Lars Brownies

I need to store the working regions for a job. Often a job has more than one
region and often it's the same group of regions as with other jobs. I could
make a separate Group Table but what do I do if the job only has one region?
If I store that info in the Job Table, I would store region info in 2
different tables. Adding a separate group for every job that has one working
region doesn't seem good either.

Any hints on how to approach this?

Thanks.

Lars
 
Are these regions pretty static (i.e. not likely to change)?

If so, you could set up your Region table so it has fields like this:
- RegionID AutoNumber Primary key
- RegionName Text
- ParentRegionID Number

Now you set up your data like this:
RegionID Region Name ParentRegionID
1 South
2 South West 1
3 South East 1
4 North
5 North West 4
5 North East 4
and so on. Effectively, South contains South West and South East, and so on.

Now when you select the combo for a job you can choose a specific region
(such as South West), or a generic region (such as South.)
 
Thanks. My example wasn't clear enough. In my case a region (which actually
is a country) could belong to multiple generic regions (groups):

There are jobs that belong to group A which has countries France, Spain and
Italy as their working area.
There are jobs that belong to group B which has countries France and Belgium
as their working area.
There are jobs that don't belong to a group but only to one or more
countries.

The purpose of using groups is that if a country is added to the working
area for a specific type of job the user only has to change this once in
stead of for all jobs that it applies to.

Would you use the table you suggested and add an extra relation table to
link the countries to the groups?

On the other hand, in this way the country table would also hold group
values, which doesn't seem right.

Lars
 
At the simplest level, you could have both a GroupID and a CountryID field
in your table, so the user can select a group or a single country.

There are other alternatives.
 
Thanks. Since some jobs don't belong to a group but do have more than one
country, I think I'll go with an extra relation table for countries:

tblR_JobCountry
JobID*
CountryID*

and a GroupID field in the Job table.

Lars
 
Lars,

I am not exactly sure what you are trying to do but if you are running
Access 2007, this seems to be a perfect chore for a multivalue field. You
would be able to assign countries to each job, as needed.

David
 
This is not an option. First because it's an A2003 application and secondly
because I need to be able to link groups of countries to different jobs.
When a group changes I need to be able to edit the group otherwise I would
have to go to every job and change the country there. Also for entering data
I would have to add the same countries to different jobs over and over
again. On top of that, experts recommend against multi value fields. See for
instance:
http://www.eggheadcafe.com/software/aspnet/33943626/multivalue-fields-and-nor.aspx

Lars
 
Yes Lars,

Many of the pundits don't like MVFs but I have using them since Access 2007
beta was first released and I have never had a problem with them. In fact,
just about every table that I create has at least one MVF.

When Microsoft first introduced MVF in Access 2007, I researched the term
and found that it pre-dated Access 2007 by many years. If other database
manufacturers don't use them then it is probably because some folks are hung
up on staying with principles that are 20 years old, IMHO.

But it is academic if you are on A2003 and don't plan to upgrade to A2007 or
A2010. Try A2007 or A2010 and see for yourself if they work in your
situation. They may not be as good as sliced bread but they are pretty close
in my opinion.

David
 
Back
Top