Table Relationship Challenge

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I am in need for some suggestions as to how I should
design the relationship between 2 tables (one for "Jobs"
and a second one for "Personal"). Throught the rest of
this explanation, I will refere to the tables as...
Tbl_A = Jobs
Tbl_B = Personal

Obviously, Tbl_A contains data about jobs (e.g. title,
job description) while Tbl_B contains data about the
employees (e.g. name, education).

So far so good... here's the problem now. In Tbl_B, I
will have (in some instances) 2 people pointing to the
same job.

The first person has been holding the job in the past and
is scheduled to leave the company in the near future.
The second person is his/her scheduled relief and works
with "employee # 1" for a short period of time during the
transition phase.

Let me provide some example data -- VERSION 1:

Tbl_A Tbl_B
RecID Title JobDesc RecID Name Education
01 Analyst ABC 01 Jack B.A.
02 Engineer XYZ 02 Ben M.S.
02 Sue M.S
03 Manager DEF 03 Carol B.S.
03 Mike B.A.
04 Analyst ABC 04 Tom B.S.


As demonstrated, in this example, the RecordIDs 02 & 03
are currently staffed by 2 individuals each.

In such case, I would use a One-To-Many relationship.
However, I am not sure if that's what I want... at this
time, the record of jobs does not equal "4" (when joined)
due the the dups. Grouping is also not an option since
the personal table contains additional fields (beyond
name and education), so I would not get the proper count.


DESING VERSION # 2:

Tbl_A Tbl_B
RecID Title JobDesc RecID Name Education
01 Analyst ABC 01 Jack B.A.
02 Engineer XYZ 02 Ben M.S.
021 Sue M.S
03 Manager DEF 03 Carol B.S.
031 Mike B.A.
04 Analyst ABC 04 Tom B.S.

In this case, all RecIDs are unique (02 & 021, 03 &
031). However, once the employee who currently holds the
position (RecID = 02, 03) leaves the company [AND THEIR
RECORD WILL BE MOVED OUT OF THIS TABLE], I have lost the
connection between the following:
- Job RecID 02 != Personal RecID 021
- Job RecID 03 != Personal RecID 031


So, again, how would you design and overcome this
problem? Again, a one-to-many relationship might not be
possible, but then... maybe you know of something I can't
think of?!

A possible solution would be to throw a message box (when
deleting or moving the primary/current employee) out of
the table. For instance, when the employee is i.e.
deleted, I get a message box that says: "Please change
RecId from temporary employee to permanent employee!"
In this case, I would get reminded to change i.e. "021"
to "02" & "031" to "03". This would ensure data
integrity... but it seems not very efficient at the same
time.

Or, if the primary employee is "flagged" (boolean =
true), I cannot delete his/her record until I set it to
false and made the relief position = true.

As you can see, I am struggling a bit w/ this problem.
Maybe there's an easy answer I just can't seem to think
of ...

Any help in this is greatly appreciated!!!

THANKS!

Tom
 
So, again, how would you design and overcome this
problem? Again, a one-to-many relationship might not be
possible, but then... maybe you know of something I can't
think of?!
Click to expand...

I'd suggest a design which reflects the reality: a many to many
relationship. If each Job can have several people working it, and (at
least temporarily) one person can have more than one Job, you can add
a *third table* JobAssignment with fields for the PersonID and the
JobID. This table could include other fields such as effective date,
comments ("filling in while X is on medical leave" for example), etc.
 
Tom:

Try a three table design:

tblJobs
JobID JobTitle JobDesc
(JobID is Autonumber Primary Key)

tblEmployees
EmpID EmpName EmpEducation EmpActive
(EmpID is Autonumber Primary Key)

tblJobAssignments
JobID EmpID
(both fields Long Integer)
(Primary Key: JobID, EmpID)
(Relationship: JobID foriegn key to tblJobs.JobID, one to many)
(Relationship: EmpID foriegn key to tblEmployees.EmpID, one to many)

tblJobs Contents (JobID contents determined by Access):
JobID JobTitle JobDesc
=================================
51 Analyst ABC
52 Engineer XYZ
53 Manager DEF
54 Analyst II QRS

tblEmployees Contents (EmpID contents determined by Access):
EmpID EmpName EmpEducation EmpActive
===================================================
121 Jack B.A. True
122 Ben M.S. True
123 Sue M.S. True
124 Carol B.S. True
125 Mike B.A. True
126 Tom B.S. True

tblJobAssignments Contents:
JobID EmpID
===============
51 121
52 122
52 123
53 124
53 125
54 126

So, now you can maintain proper counts of Jobs and Employees. You
don't have to delete or move an Employee record to delete a Job
Assignment. You could make a Job Assignment inactive in two ways:
delete a record from tblJobAssignments, or better yet, mark the
Employee as Inactive in tblEmployees. To show active assignments,
write a query that shows all Job Assignments for Employees where
EmpActive = True.

-Ken

Tom said:
I am in need for some suggestions as to how I should
design the relationship between 2 tables (one for "Jobs"
and a second one for "Personal"). Throught the rest of
this explanation, I will refere to the tables as...
Tbl_A = Jobs
Tbl_B = Personal

Obviously, Tbl_A contains data about jobs (e.g. title,
job description) while Tbl_B contains data about the
employees (e.g. name, education).

So far so good... here's the problem now. In Tbl_B, I
will have (in some instances) 2 people pointing to the
same job.

The first person has been holding the job in the past and
is scheduled to leave the company in the near future.
The second person is his/her scheduled relief and works
with "employee # 1" for a short period of time during the
transition phase.

Let me provide some example data -- VERSION 1:

Tbl_A Tbl_B
RecID Title JobDesc RecID Name Education
01 Analyst ABC 01 Jack B.A.
02 Engineer XYZ 02 Ben M.S.
02 Sue M.S
03 Manager DEF 03 Carol B.S.
03 Mike B.A.
04 Analyst ABC 04 Tom B.S.


As demonstrated, in this example, the RecordIDs 02 & 03
are currently staffed by 2 individuals each.

In such case, I would use a One-To-Many relationship.
However, I am not sure if that's what I want... at this
time, the record of jobs does not equal "4" (when joined)
due the the dups. Grouping is also not an option since
the personal table contains additional fields (beyond
name and education), so I would not get the proper count.


DESING VERSION # 2:

Tbl_A Tbl_B
RecID Title JobDesc RecID Name Education
01 Analyst ABC 01 Jack B.A.
02 Engineer XYZ 02 Ben M.S.
021 Sue M.S
03 Manager DEF 03 Carol B.S.
031 Mike B.A.
04 Analyst ABC 04 Tom B.S.

In this case, all RecIDs are unique (02 & 021, 03 &
031). However, once the employee who currently holds the
position (RecID = 02, 03) leaves the company [AND THEIR
RECORD WILL BE MOVED OUT OF THIS TABLE], I have lost the
connection between the following:
- Job RecID 02 != Personal RecID 021
- Job RecID 03 != Personal RecID 031


So, again, how would you design and overcome this
problem? Again, a one-to-many relationship might not be
possible, but then... maybe you know of something I can't
think of?!

A possible solution would be to throw a message box (when
deleting or moving the primary/current employee) out of
the table. For instance, when the employee is i.e.
deleted, I get a message box that says: "Please change
RecId from temporary employee to permanent employee!"
In this case, I would get reminded to change i.e. "021"
to "02" & "031" to "03". This would ensure data
integrity... but it seems not very efficient at the same
time.

Or, if the primary employee is "flagged" (boolean =
true), I cannot delete his/her record until I set it to
false and made the relief position = true.

As you can see, I am struggling a bit w/ this problem.
Maybe there's an easy answer I just can't seem to think
of ...

Any help in this is greatly appreciated!!!

THANKS!

Tom
Click to expand...
 
Ken:

Thanks for your prompt reply... I got this message late
and I will look into this later on tonight or tomorrow
morning.

I will let you know how this works out for me!

Thanks,
Tom

-----Original Message-----
Tom:

Try a three table design:

tblJobs
JobID JobTitle JobDesc
(JobID is Autonumber Primary Key)

tblEmployees
EmpID EmpName EmpEducation EmpActive
(EmpID is Autonumber Primary Key)

tblJobAssignments
JobID EmpID
(both fields Long Integer)
(Primary Key: JobID, EmpID)
(Relationship: JobID foriegn key to tblJobs.JobID, one to many)
(Relationship: EmpID foriegn key to
Click to expand...
tblEmployees.EmpID, one to many)
tblJobs Contents (JobID contents determined by Access):
JobID JobTitle JobDesc
=================================
51 Analyst ABC
52 Engineer XYZ
53 Manager DEF
54 Analyst II QRS

tblEmployees Contents (EmpID contents determined by Access):
EmpID EmpName EmpEducation EmpActive
===================================================
121 Jack B.A. True
122 Ben M.S. True
123 Sue M.S. True
124 Carol B.S. True
125 Mike B.A. True
126 Tom B.S. True

tblJobAssignments Contents:
JobID EmpID
===============
51 121
52 122
52 123
53 124
53 125
54 126

So, now you can maintain proper counts of Jobs and Employees. You
don't have to delete or move an Employee record to delete a Job
Assignment. You could make a Job Assignment inactive in two ways:
delete a record from tblJobAssignments, or better yet, mark the
Employee as Inactive in tblEmployees. To show active assignments,
write a query that shows all Job Assignments for Employees where
EmpActive = True.

-Ken

"Tom" <[email protected]> wrote in message
I am in need for some suggestions as to how I should
design the relationship between 2 tables (one for "Jobs"
and a second one for "Personal"). Throught the rest of
this explanation, I will refere to the tables as...
Tbl_A = Jobs
Tbl_B = Personal

Obviously, Tbl_A contains data about jobs (e.g. title,
job description) while Tbl_B contains data about the
employees (e.g. name, education).

So far so good... here's the problem now. In Tbl_B, I
will have (in some instances) 2 people pointing to the
same job.

The first person has been holding the job in the past and
is scheduled to leave the company in the near future.
The second person is his/her scheduled relief and works
with "employee # 1" for a short period of time during the
transition phase.

Let me provide some example data -- VERSION 1:

Tbl_A Tbl_B
RecID Title JobDesc RecID Name Education
01 Analyst ABC 01 Jack B.A.
02 Engineer XYZ 02 Ben M.S.
02 Sue M.S
03 Manager DEF 03 Carol B.S.
03 Mike B.A.
04 Analyst ABC 04 Tom B.S.


As demonstrated, in this example, the RecordIDs 02 & 03
are currently staffed by 2 individuals each.

In such case, I would use a One-To-Many relationship.
However, I am not sure if that's what I want... at this
time, the record of jobs does not equal "4" (when joined)
due the the dups. Grouping is also not an option since
the personal table contains additional fields (beyond
name and education), so I would not get the proper count.


DESING VERSION # 2:

Tbl_A Tbl_B
RecID Title JobDesc RecID Name Education
01 Analyst ABC 01 Jack B.A.
02 Engineer XYZ 02 Ben M.S.
021 Sue M.S
03 Manager DEF 03 Carol B.S.
031 Mike B.A.
04 Analyst ABC 04 Tom B.S.

In this case, all RecIDs are unique (02 & 021, 03 &
031). However, once the employee who currently holds the
position (RecID = 02, 03) leaves the company [AND THEIR
RECORD WILL BE MOVED OUT OF THIS TABLE], I have lost the
connection between the following:
- Job RecID 02 != Personal RecID 021
- Job RecID 03 != Personal RecID 031


So, again, how would you design and overcome this
problem? Again, a one-to-many relationship might not be
possible, but then... maybe you know of something I can't
think of?!

A possible solution would be to throw a message box (when
deleting or moving the primary/current employee) out of
the table. For instance, when the employee is i.e.
deleted, I get a message box that says: "Please change
RecId from temporary employee to permanent employee!"
In this case, I would get reminded to change i.e. "021"
to "02" & "031" to "03". This would ensure data
integrity... but it seems not very efficient at the same
time.

Or, if the primary employee is "flagged" (boolean =
true), I cannot delete his/her record until I set it to
false and made the relief position = true.

As you can see, I am struggling a bit w/ this problem.
Maybe there's an easy answer I just can't seem to think
of ...

Any help in this is greatly appreciated!!!

THANKS!

Tom
Click to expand...
.
Click to expand...
 
John:

Thanks for your prompt reply... I got this message late
and I will look into this later on tonight or tomorrow
morning.

I will let you know how this works out for me!

Thanks,
Tom
 
Ken:

I ended up working on this tonight...

At this time I have a two questions:

Q1.
When creating "tblJobAssignments", I could not make
the "JobID" the primary key due to duplications (e.g. 52
& 53) in this field.

What should the setting be? Did you mean that both
fields need to be PKs?


Q2:

Based on Q1, I have chosen "DataType = Text"; "Indexed =
Duplicates (Yes)". Once in the relationship view, I now
could not join the "tblJobAssignments.JobID"
with "tblJobs.JobID".

When creating the relationship, I now get the following
error: "Relationship must be on the same number of fields
with the same data types."

Also, I tried to checkmark all boxes:
- Enforce Referential Integrity
- Cascade Update Related Fields
- Cascade Delete Related Records

Lastly, why isn't there a checkbox for "Cascade Added
Related Records". It seems that I need to create a job
assignment when adding a new employee. What am I missing
conceptually and technically here?

Thanks again (in advance) for your feedback!!!!

Tom


-----Original Message-----
Tom:

Try a three table design:

tblJobs
JobID JobTitle JobDesc
(JobID is Autonumber Primary Key)

tblEmployees
EmpID EmpName EmpEducation EmpActive
(EmpID is Autonumber Primary Key)

tblJobAssignments
JobID EmpID
(both fields Long Integer)
(Primary Key: JobID, EmpID)
(Relationship: JobID foriegn key to tblJobs.JobID, one to many)
(Relationship: EmpID foriegn key to
Click to expand...
tblEmployees.EmpID, one to many)
tblJobs Contents (JobID contents determined by Access):
JobID JobTitle JobDesc
=================================
51 Analyst ABC
52 Engineer XYZ
53 Manager DEF
54 Analyst II QRS

tblEmployees Contents (EmpID contents determined by Access):
EmpID EmpName EmpEducation EmpActive
===================================================
121 Jack B.A. True
122 Ben M.S. True
123 Sue M.S. True
124 Carol B.S. True
125 Mike B.A. True
126 Tom B.S. True

tblJobAssignments Contents:
JobID EmpID
===============
51 121
52 122
52 123
53 124
53 125
54 126

So, now you can maintain proper counts of Jobs and Employees. You
don't have to delete or move an Employee record to delete a Job
Assignment. You could make a Job Assignment inactive in two ways:
delete a record from tblJobAssignments, or better yet, mark the
Employee as Inactive in tblEmployees. To show active assignments,
write a query that shows all Job Assignments for Employees where
EmpActive = True.

-Ken

"Tom" <[email protected]> wrote in message
I am in need for some suggestions as to how I should
design the relationship between 2 tables (one for "Jobs"
and a second one for "Personal"). Throught the rest of
this explanation, I will refere to the tables as...
Tbl_A = Jobs
Tbl_B = Personal

Obviously, Tbl_A contains data about jobs (e.g. title,
job description) while Tbl_B contains data about the
employees (e.g. name, education).

So far so good... here's the problem now. In Tbl_B, I
will have (in some instances) 2 people pointing to the
same job.

The first person has been holding the job in the past and
is scheduled to leave the company in the near future.
The second person is his/her scheduled relief and works
with "employee # 1" for a short period of time during the
transition phase.

Let me provide some example data -- VERSION 1:

Tbl_A Tbl_B
RecID Title JobDesc RecID Name Education
01 Analyst ABC 01 Jack B.A.
02 Engineer XYZ 02 Ben M.S.
02 Sue M.S
03 Manager DEF 03 Carol B.S.
03 Mike B.A.
04 Analyst ABC 04 Tom B.S.


As demonstrated, in this example, the RecordIDs 02 & 03
are currently staffed by 2 individuals each.

In such case, I would use a One-To-Many relationship.
However, I am not sure if that's what I want... at this
time, the record of jobs does not equal "4" (when joined)
due the the dups. Grouping is also not an option since
the personal table contains additional fields (beyond
name and education), so I would not get the proper count.


DESING VERSION # 2:

Tbl_A Tbl_B
RecID Title JobDesc RecID Name Education
01 Analyst ABC 01 Jack B.A.
02 Engineer XYZ 02 Ben M.S.
021 Sue M.S
03 Manager DEF 03 Carol B.S.
031 Mike B.A.
04 Analyst ABC 04 Tom B.S.

In this case, all RecIDs are unique (02 & 021, 03 &
031). However, once the employee who currently holds the
position (RecID = 02, 03) leaves the company [AND THEIR
RECORD WILL BE MOVED OUT OF THIS TABLE], I have lost the
connection between the following:
- Job RecID 02 != Personal RecID 021
- Job RecID 03 != Personal RecID 031


So, again, how would you design and overcome this
problem? Again, a one-to-many relationship might not be
possible, but then... maybe you know of something I can't
think of?!

A possible solution would be to throw a message box (when
deleting or moving the primary/current employee) out of
the table. For instance, when the employee is i.e.
deleted, I get a message box that says: "Please change
RecId from temporary employee to permanent employee!"
In this case, I would get reminded to change i.e. "021"
to "02" & "031" to "03". This would ensure data
integrity... but it seems not very efficient at the same
time.

Or, if the primary employee is "flagged" (boolean =
true), I cannot delete his/her record until I set it to
false and made the relief position = true.

As you can see, I am struggling a bit w/ this problem.
Maybe there's an easy answer I just can't seem to think
of ...

Any help in this is greatly appreciated!!!

THANKS!

Tom
Click to expand...
.
Click to expand...
 
Tom:

Q1:

Yes. Both Fields need to be the PK, in this order: JobID, EmpID. This
sets up an index that says the same employee cannot be assigned more
than once to the same job.

Q2:

tblJobAssignments.JobID and tblJobs.JobID must have the same datatype
to establish a relationship. If tblJobs.JobID is set to Autonumber,
the matching datatype in tblJobAssignments.JobID is Long.

As for the relationship options, I recommend:
- Enforce Referential Integrity Yes
- Cascade Update Related Fields No
- Cascade Delete Related Records No

Referential integrity guarantees that the JobID and EmpID foreign keys
in tblJobAssignments match records in tblJobs and tblEmployees. It
also prevents deleting a Job or Employee if they are participating in
an Assignment. This is a handy way to limit accidental deletion of
data!

I would suggest that you don't want to allow deletion of Employees and
Jobs participating in job Assignments, as they are matters of
historical record.

As far as adding related records, that is your job in forms and code.
Once your tables and relationships are properly set up, this will be
easier.

-Ken
 
Ken:

I reckon, my final question would be in respect to the 2
PKs in the tblJobAssignments.

I figured out how to use 2 PKs, but I'm still now allows
to choosse 2 AUTONUMBERS in the same table.
Click to expand...

Clarification: a table can have one and only one PK; that PK can
consist of two fields (or up to ten fields, in point of fact).
As you suggested, this last part is a required in order
to make it work (at least that's how I interpret your
suggestions).

So, again, how do I set the datatypes of 2 fields to
AUTONUMBER in the same table?
Click to expand...

You cannot, you need not, and you should not!

These fields should be Long Integers in this table; each should be
joined to the Autonumber field in the "one" side tables, but they
should not themselves be autonumbers.
 
Back
Top