Relationships

[Tom]

I need some help with creating relationships and
maintaining data integrity.

A few weeks ago, I posted a thread in which I asked how
to built a simple One-to-Many relationship between 2
tables (tblJobs & tblEmployees). Basically, I needed
a "relationship" that allows to have i.e.
- 10 employees linked to
- 8 jobs

[Note: There might be a number of positions to which I
have temporarily more than 1 employee linked to -
"transition" of a position would be such an example].

One of the answers was provided by Ken (MVP) who
suggested to use a "junction table".

Here's his reply (which will illustrate the initial task
at hand as well):


*******************************************

"Try a three table design:" [Ken]

tblJobs
JobID JobTitle
(JobID is Autonumber Primary Key)

tblEmployees
EmpID EmpName
(EmpID is Autonumber Primary Key)

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



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


tblEmployees Contents (EmpID contents determined by
Access):
EmpID EmpName
================
121 Jack
122 Ben
123 Sue
124 Carol
125 Mike
126 Tom


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


*******************************************

This design worked perfectly at that time; however, I now
need to link additional tables into such architecture.

Let's say I duplicate this "structure" for yet another
similar scenario. For instance, I might have
also "cases" that have multiple comments.

This would leave me with two independent "3-table"
designs that somehow need to be linked.

Not also do I need to link them in some fashion, I
probably (I emphasize probably since I not entirely
certain if this is the best way) will have also a One-To-
One relationship between the "tblJobs" and another table.



Essentially this is how I currently have the
relationships set up (I already know that they are wrong
due to record duplications):

RELATIONSHIPS DIAGRAM (this does not include the "3-
table" design yet)


__________RELATIONSHIPS "DIAGRAM"________________________

tbl_IncomingCommunication to tbl_IncomingCommuncition_ Ref
- linked via PK SERIALNO to FK SERIALNO
- 1 to ∞

tbl_IncomingCommunication to tbl_Distribution
- linked via PK SERIALNO to PK SERIALNO
- 1 to 1

tbl_Distribution to tbl_Location
- linked via PK SERIALNO to FK SERIALNO
- 1 to ∞

tbl_Distribution to tbl_Sub_Actions
- linked via PK SERIALNO to FK SERIALNO
- 1 to ∞

tbl_OutgoingCommunication to tbl_OutgoingCommuncition_ Ref
- linked via PK OCID to FK OCID
- 1 to ∞

tbl_OutgoingCommunication to tbl_IncomingCommunication
- linked via PK OCID to FK OCID
- 1 to 1; Left Outer Join
===============

___________________________________________

As one may realize I have some mediocre "knowledge" of
database design. But I truly know that the above design
won't work.

I truly would appreciate if someone could please provide
some help as to how I
- may should merge/breakup the tables (this may be tuff
though since I didn't provide much detail on other
fields).
- create proper relationships via using different PKs and
FKs

I truly need somebody's help here!!!


Thanks in advance,
Tom

 

[Jack MacDonald]

Tom:
Not sure whether I can fully answer your plea, but here are a couple
things to bear in mind:

1. You originally asked about a one-to-many relationship, but what
your really have is a many-to-many relationship (many employees, many
jobs). That type of relationship always requires a junction table.

2. When you add "cases", you are **probably** adding another
many-to-many relationship. For example, each employee can work with
many cases, and each case can have many employees. (Is that true??).
If true, then you need a junction table between employees and cases.
NOTE: it is the **same** employee table as used in the
employees-to-jobs relationship

3. In describing your incoming and outgoing communications tables, you
should consider having just one table, with an attribute to describe
whether it's incoming or outgoing. Ask yourself: is there any
fundamental difference between the two types of communication. If not,
then combine them into a single table.

4. As you alluded to, there is not enough info to understand what is
the relationship between communications and communications_ref.
However, you have described it as a one-to-many relationship, which
implies that each communication consists of many "refs" (whatever they
are...) Ask yourself: what are the real-world entities that need to be
tracked, and create a table for each unique type of entity.

5. One-to-one relationships are usually not required, however, your
situation may be different. A place where I have used one-to-one is
for mobile equipment such as trucks and trailers. *Every* piece of
equipment has a common set of attributes (name, make, model, year of
manufacture, etc), but different types of equipment also have unique
attributes (e.g. trucks have engines and transmissions, trailers do
not, trailers have payload capacity, trucks do not). The common
attributes go into one table, and the unique attributes go into unique
tables for each equipment type. The two unique tables are related
one-to-one with the common table. Unless a similar situation applies
for you, try to avoid one-to-one relationships. You are usually better
to include all the info in a single table.

HTH

I need some help with creating relationships and
maintaining data integrity.

A few weeks ago, I posted a thread in which I asked how
to built a simple One-to-Many relationship between 2
tables (tblJobs & tblEmployees). Basically, I needed
a "relationship" that allows to have i.e.
- 10 employees linked to
- 8 jobs

[Note: There might be a number of positions to which I
have temporarily more than 1 employee linked to -
"transition" of a position would be such an example].
[snip]



This would leave me with two independent "3-table"
designs that somehow need to be linked.

Not also do I need to link them in some fashion, I
probably (I emphasize probably since I not entirely
certain if this is the best way) will have also a One-To-
One relationship between the "tblJobs" and another table.
[snip]


__________RELATIONSHIPS "DIAGRAM"________________________

tbl_IncomingCommunication to tbl_IncomingCommuncition_ Ref
- linked via PK SERIALNO to FK SERIALNO
- 1 to ∞

tbl_IncomingCommunication to tbl_Distribution
- linked via PK SERIALNO to PK SERIALNO
- 1 to 1

=======================================================
Jack MacDonald
remove UPPERCASE LETTERS from email address
Vancouver, B.C. Canada
Info about MSAccess user-level security
www.geocities.com/jacksonmacd

 

[Tom]

Jack:

Thanks for the info... based on my generic questions, I
kinda expected to get only a generic answer. Anyhow,
you definitely helped me understand the junction table
scenario and one-to-one much better now.

Nevertheless, I am still not able to solve my problem.

I added another thread (less details)
called "Relationship teaser" today. Maybe this will
help you get a better picture of the scenario (I kept it
more broad) and hopefully you'll be able to point me in
the right direction.

Thanks,
Tom

-----Original Message-----
Tom:
Not sure whether I can fully answer your plea, but here are a couple
things to bear in mind:

1. You originally asked about a one-to-many relationship, but what
your really have is a many-to-many relationship (many employees, many
jobs). That type of relationship always requires a junction table.

2. When you add "cases", you are **probably** adding another
many-to-many relationship. For example, each employee can work with
many cases, and each case can have many employees. (Is that true??).
If true, then you need a junction table between employees and cases.
NOTE: it is the **same** employee table as used in the
employees-to-jobs relationship

3. In describing your incoming and outgoing communications tables, you
should consider having just one table, with an attribute to describe
whether it's incoming or outgoing. Ask yourself: is there any
fundamental difference between the two types of communication. If not,
then combine them into a single table.

4. As you alluded to, there is not enough info to understand what is
the relationship between communications and communications_ref.
However, you have described it as a one-to-many relationship, which
implies that each communication consists of many "refs" (whatever they
are...) Ask yourself: what are the real-world entities that need to be
tracked, and create a table for each unique type of entity.

5. One-to-one relationships are usually not required, however, your
situation may be different. A place where I have used one-to-one is
for mobile equipment such as trucks and trailers. *Every* piece of
equipment has a common set of attributes (name, make, model, year of
manufacture, etc), but different types of equipment also have unique
attributes (e.g. trucks have engines and transmissions, trailers do
not, trailers have payload capacity, trucks do not). The common
attributes go into one table, and the unique attributes go into unique
tables for each equipment type. The two unique tables are related
one-to-one with the common table. Unless a similar situation applies
for you, try to avoid one-to-one relationships. You are usually better
to include all the info in a single table.

HTH

I need some help with creating relationships and
maintaining data integrity.

A few weeks ago, I posted a thread in which I asked how
to built a simple One-to-Many relationship between 2
tables (tblJobs & tblEmployees). Basically, I needed
a "relationship" that allows to have i.e.
- 10 employees linked to
- 8 jobs

[Note: There might be a number of positions to which I
have temporarily more than 1 employee linked to -
"transition" of a position would be such an example].
[snip]



This would leave me with two independent "3-table"
designs that somehow need to be linked.

Not also do I need to link them in some fashion, I
probably (I emphasize probably since I not entirely
certain if this is the best way) will have also a One- To-
One relationship between the "tblJobs" and another table.
[snip]


__________RELATIONSHIPS "DIAGRAM"_______________________ _

tbl_IncomingCommunication to tbl_IncomingCommuncition_ Ref
- linked via PK SERIALNO to FK SERIALNO
- 1 to ∞

tbl_IncomingCommunication to tbl_Distribution
- linked via PK SERIALNO to PK SERIALNO
- 1 to 1

=======================================================
Jack MacDonald
remove UPPERCASE LETTERS from email address
Vancouver, B.C. Canada
Info about MSAccess user-level security
www.geocities.com/jacksonmacd
.