New Database - need help defining keys and relationships

[joe]

Hello All

I am struggling to create a new database structure that gives me what
I am looking for. Seems like it should be easy at first glance but I
have not figured out how to get what I want yet. Here is the problem.

I have information about projects, contractors and skills. For each
project I have I would like to create a query that displays all the
possible skills that a job could need. And then for every project I
would like to show of all the skills I have, what contractors have I
found to fulfill the need.

I started creating 3 separate tables; Projects, Skills, Contractors
and the subsequent fields I needed. Don’t know why I am so stuck here
but not sure how I can configure this so that I can list 1 project,
all skills and then any contractor(s) that meet my need for that
particular project.

Seems like I need one primary key that is common to all tables and
that allow a 1- many relationship between Projects and Skills and 1-
many for skills and contractors.

Aside from the basic table fields, can anyone provide some thoughts on
how I can structure the keys and relationships to get started?

Many thanks

 

[Steve Schapel]

Joe,

In a nutshell...

If it is possible for a Contractor to have more than one Skill, you will
need another table for ContractorSkills, where you record all the Skills
provided by each Contractor.

Do you want to also designate the Skills required for the Projects? If so,
you will similarly also need a ProjectSkills table, where you can record
this information. Otherwise, if you mean you will assume that *all* skills
may be applicable to *all* projects, you will be able to use a "Cartesian"
query based on both the Projects table and the Skills table, with no Joins
at all.

--
Steve Schapel, Microsoft Access MVP

Hello All

I am struggling to create a new database structure that gives me what
I am looking for. Seems like it should be easy at first glance but I
have not figured out how to get what I want yet. Here is the problem.

I have information about projects, contractors and skills. For each
project I have I would like to create a query that displays all the
possible skills that a job could need. And then for every project I
would like to show of all the skills I have, what contractors have I
found to fulfill the need.

I started creating 3 separate tables; Projects, Skills, Contractors
and the subsequent fields I needed. Don’t know why I am so stuck here
but not sure how I can configure this so that I can list 1 project,
all skills and then any contractor(s) that meet my need for that
particular project.

Seems like I need one primary key that is common to all tables and
that allow a 1- many relationship between Projects and Skills and 1-
many for skills and contractors.

Aside from the basic table fields, can anyone provide some thoughts on
how I can structure the keys and relationships to get started?

Many thanks

__________ Information from ESET Smart Security, version of virus signature database 4142 (20090609) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[joe]

Joe,

In a nutshell...

If it is possible for a Contractor to have more than one Skill, you will
need another table for ContractorSkills, where you record all the Skills
provided by each Contractor.

Do you want to also designate the Skills required for the Projects?  Ifso,
you will similarly also need a ProjectSkills table, where you can record
this information.  Otherwise, if you mean you will assume that *all* skills
may be applicable to *all* projects, you will be able to use a "Cartesian"
query based on both the Projects table and the Skills table, with no Joins
at all.

--
Steve Schapel, Microsoft Access MVP
















__________ Information from ESET Smart Security, version of virus signature database 4142 (20090609) __________

The message was checked by ESET Smart Security.

http://www.eset.com- Hide quoted text -

- Show quoted text -

Steve,

Thanks for the reply. I thought a cartesian product maybe the right
answer. So, does creating a cartesian have any negative downstream
impacts in creating queries or reports? I have always avoided this
practice as I have learned that you never want to have the dreaded
Cartesian . .

Thanks

 

[Steve Schapel]

Joe,

To be honest, I didn't know the Cartesian was "dreaded". I guess if you've
got very large numbers of records in both base tables, you would have to
think carefully about how you manage it. In any case, I do not imagine this
would apply in your example. Mostly you will be relating to one Project at
a time, in which case the number of records returned by the Cartesion query
will just be the same as the number of records in the Skills table. After
you have assigned the Contractors to the Projects, then the Skills invloved
in the Project will be defined according to the Skills of the Contractors
assigned, so (if I understand you correctly) it is doubtful that you will be
using the Cartesian query much unless you want to assign another Contractor
to a Project, or if you want to report for example on such things as a list
of skills not catered for on the project. Long way of saying I don't see
any problem. However, I am somewhat surprised that you don't have a need to
specifically identify the Skills required for each Project.

--
Steve Schapel, Microsoft Access MVP

Thanks for the reply. I thought a cartesian product maybe the right
answer. So, does creating a cartesian have any negative downstream
impacts in creating queries or reports? I have always avoided this
practice as I have learned that you never want to have the dreaded
Cartesian . .

__________ Information from ESET Smart Security, version of virus signature database 4142 (20090609) __________

The message was checked by ESET Smart Security.

http://www.eset.com