Join tables?

[chrizzis]

Hi,
I have been tasked with designing a member skills/certifications/education database for my unit with the ability to filter desired skills for rapid deployment. I am not a database guy, so I have been using the internet for research on database concepts. Cross section of tables created so far:

tblMbr:
mbr_ID (key)
first_name
etc...
------------------ tables tracking member skills
tblSkills: a list of skills - Welder, Pipefitter, etc...
skill_ID (key)
skill_name
skill_description

tblExperience: I want to make this a drop-down list in the form
exp_ID (key)
exp_lvl
years_exp

tblMbrSkills: join table with multiple keys (from Access help)
mbr_ID (key)
skill_ID (key)
exp_ID (key)
------------------ tables tracking member certifications
tblCerts: a list of certs. I want to make this an expandable drop-down list
cert_ID (key)
cert_name

tblMbrCerts: join table
mbr_ID (key)
cert_ID (key)
cert_date
------------------ tables tracking member degrees
tblSchool: a list of schools
school_ID (key)
school_name
etc...

tblDegreeType: a list of types - AS, BS, etc...
degree_type_ID (key)
degree_type

tblDegreeMajor: a list of majors - Computer Science, etc...
degree_major_ID (key)
major

tblDegree: join table
degree_ID (key)
degree_type_ID (key)
degree_major_ID (key)

tblMbrDegrees: join table
mbr_ID (key)
degree_ID (key)
school_ID (key)
attended_start
attended_end

I set up the relationships already. Did I conceptually and physically set the join tables up correctly?

Thanks in advance,
-C

 

[neileg]

This doesn't look right

tblMbrSkills: join table with multiple keys (from Access help)
mbr_ID (key)
skill_ID (key)
exp_ID (key)

Is there a one to one match of skills and experience?

I'm not aware enough of the US eductaion system, but in the UK there are new degree majors ever other day! I wonder whether it is worth having a lookup table if the same applies in the US.

 

[chrizzis]

You make a good point about the majors. Would deleting the tblDegreeMajor table and editing the join table to this:

tblDegree: join table
degree_ID (key)
degree_type_ID (key)
degree_major

... be a step in the right direction?

I made the tblExperience table a lookup table that I joined on a one-many relationship with the tblMbrSkills table, but as I was looking through the data types for one of the tables, I noticed the lookup tab in the lower part of the window. Can I just make the reference there, or is that different than in the relationships window?

There is a one to one match of mbr-skill-experience. Am I thinking about it incorrectly? I wanted to match the skill and experience within the context of the member. Thinking about this from a user standpoint, I wanted the member to be able to add new skills from a lookup table (or create a new skill that is not in the table) and assign his experience level (from a lookup table) to the skill.

-C

 

[neileg]

You make a good point about the majors. Would deleting the tblDegreeMajor table and editing the join table to this:

tblDegree: join table
degree_ID (key)
degree_type_ID (key)
degree_major

... be a step in the right direction?

I think so, but you haven't really explained what you are doing in any detail. Only you can tell.

I made the tblExperience table a lookup table that I joined on a one-many relationship with the tblMbrSkills table, but as I was looking through the data types for one of the tables, I noticed the lookup tab in the lower part of the window. Can I just make the reference there, or is that different than in the relationships window?

Table level lookups are a nightmare. Don't do it!

There is a one to one match of mbr-skill-experience. Am I thinking about it incorrectly? I wanted to match the skill and experience within the context of the member. Thinking about this from a user standpoint, I wanted the member to be able to add new skills from a lookup table (or create a new skill that is not in the table) and assign his experience level (from a lookup table) to the skill.

That clarifies matters. I had assumed that the experience was going to be descriptive not a value.

 

[chrizzis]

Table level lookups are a nightmare. Don't do it!

My interpretation of this is NOT to set up the lookup via the lookup tab for the data type in the table. The way I set it up (and I very well could be wrong - researching via the internet leads to incorrect interpretations of how things should be done) is:

I created reference tables (I thought it was the same as a lookup table):

tblRef_degreeTypes
degree_type_ID (PK)
degree_type (such as AA, AS, BA, BS, etc.)

tblRef_schools
school_ID (PK)
school_name
school_location

I wanted these to be combo boxes in the form. I wanted them to be able to expand the ref tables if the user needs to create a choice that is not in the list.

I created junction tables (using Access help docs - "Define Relationships Between Tables")

tblJoin_degree
degree_ID (PK)
degree_type_ID (PK - help docs told me this would act as an FK)
degree_major

tblJoin_mbrDegrees
mbr_ID (PK - help docs told me this would act as an FK)
degree_ID (PK - help docs told me this would act as an FK)
school_ID (PK - help docs told me this would act as an FK)
attended_start
attended_end

Then I linked them in the relationships window, enforcing referential integrity. I also linked tblJoin_mbrDegrees.mbr_ID to tbl_mbr.mbr_ID (PK) and enforced referential integrity. I would love to take a screenshot and show it to you to avoid confusion, but it seems I need to host the image somewhere...

 

[neileg]

OK, that sounds fine to me.