Database-design question

[Lars Brownies]

I'm designing a database which tracks people's experience and preference for
working abroad. People apply at least for one or more categories of jobs and
sometimes they also have preferences for certain countries for those
categories. I'm thinking about the following many-table for preferences:
ID_preference
ID_person
ID_category
ID_country

Btw: If they change their preferences I don't need to keep the former
preferences.

For experience and current or future workplace I'm thinking about a table
like:
ID_experience
ID_person
ID_category
ID_country
Date_start
Data_end
Notes (A255)

Since the tables have much similarity I'm doubting if I should combine them
into one table? In that case the date_start, date_end, and notes fields
would remain empty for the preferences.

Can someone advice me if this is a good idea? Or should I keep those tables
separated?

Thanks in advance.

Lars

 

[KARL DEWEY]

Use one table for prople with a primary key, Autonumber works very well.
Second table to record their preference like this --
tblPref --
PrefID - Autonumber primary key
PeopleID - Number - Long integer - foreign key to match people table primary
key
PrefType - text (or number if you will use a table listing the varrious
types to pick from to reduce typing errors)
PrefTypeSub - Same as above - (subtype for country would be list of
countries to pick from.)
PrefRate - Number - Long integer - order of preference

Maybe allow picking a maximum of 3 from each type.

Use a form/subform to enter/display people/preference like this
Doe, John L Xxxx Yyyy Zzzz
1 England
2 Brazil
3 Panama
4 Tropical
5 France

 

[Jeff Boyce]

Lars

It sounds like you are talking about persons, job categories, and countries,
plus some interaction among those. I couldn't tell from your description
whether job categories are limited by country, or if all job categories are
available in all countries.

It seems like you are saying that one person could have one/more job
categories (i.e., "preferences"). That implies that you'd need a
junction/resolver table between persons and job categories.

It also seems like you are saying that a particular combination of person &
job category may also have (?one or many?!) countries associated with it.
That sounds like yet another junction/resolver table, between your
person-in-category junction table and your countries table.

Finally, you mention that you don't care to keep the "history"... that
implies that you need to know when these selections happened, i.e., a
date/time field, so you can select the most recent. I'll point out that it
may be less work to keep the history and just enter the new valid
combinations than to try to wipe out any/all older ones. To be
conservatively safe, you would need to add the new ones first, to make sure
you have something for a person, before then deleting the old ones. Doable,
yes. Necessary, perhaps not.

Does that analysis match your situation? Does it offer a different
perspective on a possible table structure?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Lars Brownies]

Thanks Jeff, Karl for useful tips and analysis,

It makes me realize the specs aren't clear enough to make the datamodel yet.
Back to the customer and the drawing board.

Lars