Relationship

[Jamie]

I am making a database that tracks employee stats on a
daily basis under each manager. The problem I have
encountered is if an employee changes managers. How do I
capture what each employee did under their assigned
manager at the time? Here are the tables I have:
Manager Table
-Manager Badge (Primary Key)
-Manager Name

Associate Table
-Assoicate Badge (Primary Key)
-Associate Name
-Manager Badge

I have a One to Many relationship from the manager table
to the associate table by Manager Badge. Then each stat I
am tracking I have a one to many relationship from the
associate table to the "stat table" by Associate Badge.

Thank You!

 

[Roger Carlson]

A one-to-many relationship does not accurately represent your real-world
problem. Each manager can manage one or more employees, but each employee
can have OVER TIME, one or more managers. This makes is a Many-to-Many
relationship.

Therefore, you have to create a linking table in which you will store the
Manager Badge and the Assoicate Badge. ( You will no longer store the
Manager Badge in the Associate Table). Then you create two one-to-many
relationships between the two main table and the linking table with the
linking table being on the Many side of each.

Now, you will have a relationship between the linking table and the "stat"
table (and NOT from the Associate table), since the real relationship is
between the an Associate with a particular Manager and the "stats". It
should look something like this:

Manager Table M-A Associate Table
========== =========== =============
ManBadge (pk)------<ManBadge(fk) |---- AssocBadge (pk)
ManName AssocBadge(fk) >--| AssocName
|----- MAID(pk)(autonumber)
|
Stats Table |
========= |
StatsID(pk) |
MAID(fk)>-----|

 

[Jamie Flynn]

Thank You for your help! I have set up my tables and
relationships, however, I still am running into a
problem. There are many different stats that I am
tracking each having their own table. Therefore, will I
have the stat table related to each stat through the stat
id?
Also, as for importing the information for example into
the Call Score table I am not sure what to include in this
table.
Would it look like this?
Stat Table Call Score
StatID (pk)----------StatID (fk)
Maid (fk) Maid
Score
Length
NetBenefits
Thank You for your help!!

-----Original Message-----
A one-to-many relationship does not accurately represent your real-world
problem. Each manager can manage one or more employees, but each employee
can have OVER TIME, one or more managers. This makes is a Many-to-Many
relationship.

Therefore, you have to create a linking table in which you will store the
Manager Badge and the Assoicate Badge. ( You will no longer store the
Manager Badge in the Associate Table). Then you create two one-to-many
relationships between the two main table and the linking table with the
linking table being on the Many side of each.

Now, you will have a relationship between the linking table and the "stat"
table (and NOT from the Associate table), since the real relationship is
between the an Associate with a particular Manager and the "stats". It
should look something like this:

Manager Table M-

A Associate Table

 

[Roger Carlson]

I can't answer those questions. I don't know what 'stats' are (oh I could
guess, but what if I guess wrong?). I don't know why they each have their
own table. I don't know what CallScore is, nor do I know the relationships
between Stats and CallScore. Lastly, I don't know what information you are
importing and how.

You have to understand that in designing a database it is crucial to
understand the Entities (tables) and what they represent AND what exact
relationships those Entities have with each other. And I don't know your
business rules.

On my website, I have a Tutorials section, part of which concentrates on
design. http://www.rogersaccesslibrary.com/Tutorials.html

You might want to download a couple of the Database Design tutorials and
walk through the process. This might help you to understand your own design
issues.