Help needed with relationships

[shaneo via AccessMonster.com]

Hi there

I am trying to design a new database used to track people registering with my
company and also different company information.

I work for a recruitment agency that deals with a number of temp staff and
also deals with a number of companies that we supply the temp staff to.

I will break my question into 2 parts (1 for the temp staff & the other for
the company info)

How it will work..

A temp employee will come into sign up with the agency I work for. That
persons details with be entered into a table called tblPersonalInformation

tblPersonalInformation contains all the persons personal details

tblPersonaleInformation
PersonalInformationID
chrSurname
chrForename
etc
etc

A person may have many skills so I thought of making a seperate table (other
temps will have the same skills)
tblActualSkills
ActualSkillID
chrSkill

A person may have many different job bookings so I made a seperate table for
bookings
tblBookingInformation
BookingInformationID
chrJobTitle
curSalary
etc
etc
etc


Joining them is now where I get a little confused. This is what I was
thinking would work but it doesn't seem to work.

tblPersonaleInformation tblActualSkill
tblBookingInformation
PersonalInformationID ActualSkillID
BookingInformationID
chrSurname chrSkill
chrJobTitle
chrForename
curSalary
etc
chrCompanyName
etc
etc
etc
etc

PersonalInformationID


1. Adding the field PersonalInformationID to the tbl tblBookingInformation
and then creating a 1- Many relationship between tblPersonalInformation &
tblBookingInformation.

2. Creating a new tbl called tblPersonalSkills adding the fields
PersonalSkillsID (Primary Key)
BookingInformationID
ActualSkillID

Then creating a join between the tblPersonalSkills & tblBookingInformation &
then creating another join between tblPersonalSkills & tblActualSkills.

My reason for thinking that I need to create a new table is that lots of
bookings can share skills.


Am I going in the right direction or should I replan how to create these
relationships??

Part 2.

There will be a number of different companies so I have created a tbl for
them

tblCompanyInformation
CompanyInformationID
chrCompanyName
chrCompanyContact
chrAddress1
etc
etc


I don't know if I should bother with the next part but I was thinking that it
might be a must.

A company may employee a number of different temps.
Do I need to create a relationship now between tblBookingInformation &
tblCompanyInformation?

As the will contain something in common??


Hope someone can help out.

Thanks for taking the time to read this.

 

[Vincent Johns]

Hi there

I am trying to design a new database used to track people registering with my
company and also different company information.

I work for a recruitment agency that deals with a number of temp staff and
also deals with a number of companies that we supply the temp staff to.

I will break my question into 2 parts (1 for the temp staff & the other for
the company info)

How it will work..

A temp employee will come into sign up with the agency I work for. That
persons details with be entered into a table called tblPersonalInformation

tblPersonalInformation contains all the persons personal details

tblPersonaleInformation
PersonalInformationID
chrSurname
chrForename
etc
etc

A person may have many skills so I thought of making a seperate table (other
temps will have the same skills)
tblActualSkills
ActualSkillID
chrSkill

If you wish to use this record to describe one of the skills that the
temp employee has, then you need to add a field linking to the
employee's record. For the same skill type
([tblActualSkills].[chrSkill]) for another employee, you'd use a
separate record with the same [chrSkill] value.

Or, you can change [chrSkill] to be a foreign key linking to a
[tblSkills] Table, where you'd include more detailed information about a
specific skill, and several records in [tblActualSkills] could link
there, one for each employee possessing the skill.

A person may have many different job bookings so I made a seperate table for
bookings
tblBookingInformation
BookingInformationID
chrJobTitle
curSalary
etc
etc
etc

This is intriguing. Do you really want the person to be paid
differently (see [curSalary], which looks to me like pay rate) according
to which job he's working on? That would certainly inspire me to want
to spend as much time as possible on the highest-paying job, maybe
regardless of deadlines.

One of the "etc" fields had better be a link (foreign key) to the
[tblPersonalInformation] Table, otherwise you won't know whom you're
talking about in this record.

Joining them is now where I get a little confused. This is what I was
thinking would work but it doesn't seem to work.

tblPersonaleInformation tblActualSkill
tblBookingInformation
PersonalInformationID ActualSkillID
BookingInformationID
chrSurname chrSkill
chrJobTitle
chrForename
curSalary
etc
chrCompanyName
etc
etc
etc
etc

PersonalInformationID


1. Adding the field PersonalInformationID to the tbl tblBookingInformation
and then creating a 1- Many relationship between tblPersonalInformation &
tblBookingInformation.

Actually, you need not worry about taking some overt action to create a
1:Many relationship. If your key on the "one" side is guaranteed to be
unique, as it must be if it's the primary key, or if it has a "No
Duplicates" index attached to it, then Access will figure out that it's
the "1" side of the relationship. But it won't mark it as such in the
Relationships window unless you specify that you want referential
integrity enforced, and that may not work if the data in the two Tables
at the time you try to set the relationship up are inconsistent.

2. Creating a new tbl called tblPersonalSkills adding the fields
PersonalSkillsID (Primary Key)
BookingInformationID
ActualSkillID

Then creating a join between the tblPersonalSkills & tblBookingInformation &
then creating another join between tblPersonalSkills & tblActualSkills.

How I keep track of what needs to be joined is to choose names for the
primary keys that clearly identify what they are, and you are already
doing this. (I think you're doing nearly what I normally do, appending
"ID" to the end of the Table name, to get the key name. But it works
best if you don't also have other kinds of fields whose names end in "ID".)

If a record needs to refer to some record in another table, such as
[tblPersonalInformation], then you include in the record a field with
the name [PersonalInformationID], creating a foreign key matching the
name of the other Table's primary key.

Since the names match, Access can notice this and, when you're setting
up new Queries, can automatically add links. Sometimes it adds too
many, but deleting the extra ones is usually easier than defining new ones.

In the Relationships window, if the names match, it's easy to determine
which ones you want to link.

My reason for thinking that I need to create a new table is that lots of
bookings can share skills.

I'm not sure what you mean by "personal" vs. "actual" skills, but if
several bookings share one skill, then each booking should have a
foreign-key field linking it to the skill. If one booking can also have
several linked skills (as in "I need a German-speaking Access expert"),
then I suggest you add a table in which each record specifies one skill
and one booking using that skill, and link it to both the skills Table
and the bookings Table. But don't do that if you don't need to.

Am I going in the right direction or should I replan how to create these
relationships??

Part 2.

There will be a number of different companies so I have created a tbl for
them

tblCompanyInformation
CompanyInformationID
chrCompanyName
chrCompanyContact
chrAddress1
etc
etc

Umm... What is [chrCompanyContact]? This looks suspiciously like a
reference to contact information, rather than a tiny little text string.
If so, change it to be a foreign key pointing to your contacts Table.
If you do that, you might not need [chrAddress1], unless that
represents a shipping address.

It's not a bad idea to include a detailed description of what each field
means. This can go into the Description field in Table Design View, and
it is displayed in the status bar at the bottom of the database window
when you select a field.

I don't know if I should bother with the next part but I was thinking that it
might be a must.

A company may employee a number of different temps.
Do I need to create a relationship now between tblBookingInformation &
tblCompanyInformation?

As the will contain something in common??

Instead of thinking about what relationships I will define among Tables,
I usually think in terms of what keys I have to include as fields in a
record. For example, if an activity involves some human being, then a
link to [tblPersonalInformation] appears called for.

One problem: If *two* such links are called for, such as one for an
employee and another for his supervisor, then they can't both have the
same name as the other Table's primary key, and Access won't be able to
link them automatically. If you need to do that, then when you're
defining a Query to display both names (for example), you'll have to
paste two references to the table into the Query's Design View, and one
field will link to one copy of the Table and the other field will link
to the other copy. You will have to do that linking yourself (sorry
about that). But I still suggest keeping the field names similar, such
as [SupvPersonalInformationID] for the second link. It won't help
Access figure anything out, but it could save you some frazzled nerves
when you find yourself managing hundreds of field references.

Hope someone can help out.

Thanks for taking the time to read this.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.