A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

when to use many-many relationships or different data bases



 
 
Thread Tools Display Modes
  #1  
Old December 14th, 2009, 05:26 PM posted to microsoft.public.access.tablesdbdesign
Rak
external usenet poster
 
Posts: 12
Default when to use many-many relationships or different data bases

I am trying to create an operational database. We hire temporary staff
through several different employment agencies to complete work on projects
for various clients. The term of employment is linked to individual projects
which are time limited and include different assignments within each project.
Many of the people we use have worked on many different projects at many
different times including several years. I started out thinking I needed 5
different tables and needed to use a many-many relationship. Here are the 5
tables I identified with some types of data:
Employee_Personal (usual type data)
Employment_History (includes Dates hired, Projects assigned at different
times, employment title per project, performance records; rehire status)
Agency (related to each employee, # provided for each project; performance
of employees provided)
Project (Clients, Dates, subjects, teams needed, staff needed)
Accounting [(Hours to complete project-administration pre training,
training, execution and administrative client reporting); (staff cost factors
computed for number of hours *title pay); (computer cost and needs per
project); space cost and needs per project)]

I started looking at the tables to try to see what relationships I needed
and if I needed junction tables. I didn’t know how to handle the different
dates of employment and different assignments for each employee and wondered
if I could use multi-valued fields or value lists for this data. Then I
started doubting if I was going in the right direction and thought I’d see if
I could get any help.

Ads
  #2  
Old December 14th, 2009, 06:24 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default when to use many-many relationships or different data bases

Of course, this needs to be dealt with as a database design issue, not just
the linkage aspect.

You'll need to decide what the most "granular" level thing you need to
database regarding employment for a project. Then instances of that shoudl
probably be a table. Most likely this instances of continuous employment of
one person for one project.
  #3  
Old December 14th, 2009, 06:24 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default when to use many-many relationships or different data bases

The way you handle many-many relationships in Access is via a junction
table.

It sounds like you are saying that:

One Project can have many Tasks.
One Task can be assigned to (any-of-many) Staff
One Staff can be working for (any-of-many) Agencies

Sounds like:
[Project] table - info specific to a project
[Task] table - Task-specific info, including ProjectID (to which Project
does this task belong?)
[Agency] table - list of agencies and agency-specific info
[Staff] table - AKA [Employee] table
[AgencyStaff] table - a junction table, showing who worked for which
agency during which time frame
[TaskAssignment] table - a junction table, showing which AgencyStaffID
worked on which TaskID

But you're closer to your situation. Does the above make sense?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"RAK" wrote in message
...
I am trying to create an operational database. We hire temporary staff
through several different employment agencies to complete work on projects
for various clients. The term of employment is linked to individual
projects
which are time limited and include different assignments within each
project.
Many of the people we use have worked on many different projects at many
different times including several years. I started out thinking I needed 5
different tables and needed to use a many-many relationship. Here are the
5
tables I identified with some types of data:
Employee_Personal (usual type data)
Employment_History (includes Dates hired, Projects assigned at different
times, employment title per project, performance records; rehire status)
Agency (related to each employee, # provided for each project; performance
of employees provided)
Project (Clients, Dates, subjects, teams needed, staff needed)
Accounting [(Hours to complete project-administration pre training,
training, execution and administrative client reporting); (staff cost
factors
computed for number of hours *title pay); (computer cost and needs per
project); space cost and needs per project)]

I started looking at the tables to try to see what relationships I needed
and if I needed junction tables. I didn't know how to handle the different
dates of employment and different assignments for each employee and
wondered
if I could use multi-valued fields or value lists for this data. Then I
started doubting if I was going in the right direction and thought I'd see
if
I could get any help.



  #4  
Old December 14th, 2009, 06:38 PM posted to microsoft.public.access.tablesdbdesign
Dorian
external usenet poster
 
Posts: 542
Default when to use many-many relationships or different data bases

Only you can determine how things relate to eachh other since you know your
business rules.
I can envisage an employee working for multiple agencies possible more than
one simultaneously. Whether this could happen during a project you would know.
An employee might work on more than one project simultaneously possibly for
different agencies.
Actually it seems like you are handling several distinct functions in one
database. You have an Employee system, a Project system and an Accounting
system. Will these all be used by the same people. Possibly you may need
multiple front-ends for different functions.
Of course, since I dont know your systems this may all be inaccurate.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"RAK" wrote:

I am trying to create an operational database. We hire temporary staff
through several different employment agencies to complete work on projects
for various clients. The term of employment is linked to individual projects
which are time limited and include different assignments within each project.
Many of the people we use have worked on many different projects at many
different times including several years. I started out thinking I needed 5
different tables and needed to use a many-many relationship. Here are the 5
tables I identified with some types of data:
Employee_Personal (usual type data)
Employment_History (includes Dates hired, Projects assigned at different
times, employment title per project, performance records; rehire status)
Agency (related to each employee, # provided for each project; performance
of employees provided)
Project (Clients, Dates, subjects, teams needed, staff needed)
Accounting [(Hours to complete project-administration pre training,
training, execution and administrative client reporting); (staff cost factors
computed for number of hours *title pay); (computer cost and needs per
project); space cost and needs per project)]

I started looking at the tables to try to see what relationships I needed
and if I needed junction tables. I didn’t know how to handle the different
dates of employment and different assignments for each employee and wondered
if I could use multi-valued fields or value lists for this data. Then I
started doubting if I was going in the right direction and thought I’d see if
I could get any help.

  #5  
Old December 15th, 2009, 09:11 AM posted to microsoft.public.access.tablesdbdesign
rolaaus[_2_]
external usenet poster
 
Posts: 16
Default when to use many-many relationships or different data bases

One question that I have, after reading your post is when you mention "how to
handle the different dates of employment and different assignments for each
employee".

Here's my question. Does your system *need* to link the dates of a project
with the dates of an employees start/end date? In other words, let's say for
examples sake, that you have an employee that started working for your
company on Feb 1, 2008. He was assigned to a project that happened to have
started on Jan 1, 2008. You didn't mentioned tasks, but another person did,
so there's another factor.

What if you have one employee that starts a task/project and isn't able to
complete it for whatever reason (fired, quits, it doesn't matter why) and you
have to hire someone new.

This is why I ask the question, does your system need to keep track of
employees start/end dates, and !directly link! them to other dates (projects
or tasks) in the system?

Usually project tracking systems are really going to care. But if this
system is going to be used by the "bean counters" (accountants and/or
executive level - VP, etc) then it probably will matter, a lot!

"RAK" wrote:

I am trying to create an operational database. We hire temporary staff
through several different employment agencies to complete work on projects
for various clients. The term of employment is linked to individual projects
which are time limited and include different assignments within each project.
Many of the people we use have worked on many different projects at many
different times including several years. I started out thinking I needed 5
different tables and needed to use a many-many relationship. Here are the 5
tables I identified with some types of data:
Employee_Personal (usual type data)
Employment_History (includes Dates hired, Projects assigned at different
times, employment title per project, performance records; rehire status)
Agency (related to each employee, # provided for each project; performance
of employees provided)
Project (Clients, Dates, subjects, teams needed, staff needed)
Accounting [(Hours to complete project-administration pre training,
training, execution and administrative client reporting); (staff cost factors
computed for number of hours *title pay); (computer cost and needs per
project); space cost and needs per project)]

I started looking at the tables to try to see what relationships I needed
and if I needed junction tables. I didn’t know how to handle the different
dates of employment and different assignments for each employee and wondered
if I could use multi-valued fields or value lists for this data. Then I
started doubting if I was going in the right direction and thought I’d see if
I could get any help.

  #6  
Old December 15th, 2009, 08:51 PM posted to microsoft.public.access.tablesdbdesign
Rak
external usenet poster
 
Posts: 12
Default when to use many-many relationships or different data bases



"Dorian" wrote:

Only you can determine how things relate to eachh other since you know your
business rules.
I can envisage an employee working for multiple agencies possible more than
one simultaneously. Whether this could happen during a project you would know.
An employee might work on more than one project simultaneously possibly for
different agencies.
Actually it seems like you are handling several distinct functions in one
database. You have an Employee system, a Project system and an Accounting
system. Will these all be used by the same people. Possibly you may need
multiple front-ends for different functions.
Of course, since I dont know your systems this may all be inaccurate.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"RAK" wrote:

I am trying to create an operational database. We hire temporary staff
through several different employment agencies to complete work on projects
for various clients. The term of employment is linked to individual projects
which are time limited and include different assignments within each project.
Many of the people we use have worked on many different projects at many
different times including several years. I started out thinking I needed 5
different tables and needed to use a many-many relationship. Here are the 5
tables I identified with some types of data:
Employee_Personal (usual type data)
Employment_History (includes Dates hired, Projects assigned at different
times, employment title per project, performance records; rehire status)
Agency (related to each employee, # provided for each project; performance
of employees provided)
Project (Clients, Dates, subjects, teams needed, staff needed)
Accounting [(Hours to complete project-administration pre training,
training, execution and administrative client reporting); (staff cost factors
computed for number of hours *title pay); (computer cost and needs per
project); space cost and needs per project)]

I started looking at the tables to try to see what relationships I needed
and if I needed junction tables. I didn’t know how to handle the different
dates of employment and different assignments for each employee and wondered
if I could use multi-valued fields or value lists for this data. Then I
started doubting if I was going in the right direction and thought I’d see if
I could get any help.


First to clarify: staff can only be hired thru one agency during a current
year but they can work on many different projects
You said "Possibly you may need
multiple front-ends for different functions." What do you mean by this? I thought one of the strengths of Microsoft Access was to contain all relational data in one data base. Are you thinking that I need three databases? I would appreciate your views.

  #7  
Old December 15th, 2009, 08:56 PM posted to microsoft.public.access.tablesdbdesign
Rak
external usenet poster
 
Posts: 12
Default when to use many-many relationships or different data bases



"Jeff Boyce" wrote:

The way you handle many-many relationships in Access is via a junction
table.

It sounds like you are saying that:

One Project can have many Tasks.
One Task can be assigned to (any-of-many) Staff
One Staff can be working for (any-of-many) Agencies

Sounds like:
[Project] table - info specific to a project
[Task] table - Task-specific info, including ProjectID (to which Project
does this task belong?)
[Agency] table - list of agencies and agency-specific info
[Staff] table - AKA [Employee] table
[AgencyStaff] table - a junction table, showing who worked for which
agency during which time frame
[TaskAssignment] table - a junction table, showing which AgencyStaffID
worked on which TaskID

But you're closer to your situation. Does the above make sense?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"RAK" wrote in message
...
I am trying to create an operational database. We hire temporary staff
through several different employment agencies to complete work on projects
for various clients. The term of employment is linked to individual
projects
which are time limited and include different assignments within each
project.
Many of the people we use have worked on many different projects at many
different times including several years. I started out thinking I needed 5
different tables and needed to use a many-many relationship. Here are the
5
tables I identified with some types of data:
Employee_Personal (usual type data)
Employment_History (includes Dates hired, Projects assigned at different
times, employment title per project, performance records; rehire status)
Agency (related to each employee, # provided for each project; performance
of employees provided)
Project (Clients, Dates, subjects, teams needed, staff needed)
Accounting [(Hours to complete project-administration pre training,
training, execution and administrative client reporting); (staff cost
factors
computed for number of hours *title pay); (computer cost and needs per
project); space cost and needs per project)]

I started looking at the tables to try to see what relationships I needed
and if I needed junction tables. I didn't know how to handle the different
dates of employment and different assignments for each employee and
wondered
if I could use multi-valued fields or value lists for this data. Then I
started doubting if I was going in the right direction and thought I'd see
if
I could get any help.



.
Yes, thanks. This is the direction I was heading but got a little lost when I started working on relational links and defining the foreign keys. The AgencyStaff junction table appears to take care of the relationships for staff & agency. But I’m not sure what relationships are defined in the TaskAssignment junction table. Can I impose upon you for a little more detail and perhaps a light will shine for me on how to handle the accounting data.

To clarify your conclusions:
One Project can have many Tasks. Yes
One Task can be assigned to (any-of-many) Staff Yes
One Staff can be working for (any-of-many) Agencies No, not in same year

  #8  
Old December 15th, 2009, 09:03 PM posted to microsoft.public.access.tablesdbdesign
Rak
external usenet poster
 
Posts: 12
Default when to use many-many relationships or different data bases



"rolaaus" wrote:

One question that I have, after reading your post is when you mention "how to
handle the different dates of employment and different assignments for each
employee".

Here's my question. Does your system *need* to link the dates of a project
with the dates of an employees start/end date? In other words, let's say for
examples sake, that you have an employee that started working for your
company on Feb 1, 2008. He was assigned to a project that happened to have
started on Jan 1, 2008. You didn't mentioned tasks, but another person did,
so there's another factor.

What if you have one employee that starts a task/project and isn't able to
complete it for whatever reason (fired, quits, it doesn't matter why) and you
have to hire someone new.

This is why I ask the question, does your system need to keep track of
employees start/end dates, and !directly link! them to other dates (projects
or tasks) in the system?

Usually project tracking systems are really going to care. But if this
system is going to be used by the "bean counters" (accountants and/or
executive level - VP, etc) then it probably will matter, a lot!

"RAK" wrote:

I am trying to create an operational database. We hire temporary staff
through several different employment agencies to complete work on projects
for various clients. The term of employment is linked to individual projects
which are time limited and include different assignments within each project.
Many of the people we use have worked on many different projects at many
different times including several years. I started out thinking I needed 5
different tables and needed to use a many-many relationship. Here are the 5
tables I identified with some types of data:
Employee_Personal (usual type data)
Employment_History (includes Dates hired, Projects assigned at different
times, employment title per project, performance records; rehire status)
Agency (related to each employee, # provided for each project; performance
of employees provided)
Project (Clients, Dates, subjects, teams needed, staff needed)
Accounting [(Hours to complete project-administration pre training,
training, execution and administrative client reporting); (staff cost factors
computed for number of hours *title pay); (computer cost and needs per
project); space cost and needs per project)]

I started looking at the tables to try to see what relationships I needed
and if I needed junction tables. I didn’t know how to handle the different
dates of employment and different assignments for each employee and wondered
if I could use multi-valued fields or value lists for this data. Then I
started doubting if I was going in the right direction and thought I’d see if
I could get any help.


in answer to your question:"Does your system *need* to link the dates of a
project
with the dates of an employees start/end date?" Yes In addition the data needs to include data on each employee as to when & why released. this type of information is needed to answer questions by our clients on employee performance as well as for statistical reports to our clients. It is also used internally in selecting employees for future projects.

  #9  
Old December 15th, 2009, 10:18 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default when to use many-many relationships or different data bases

If you care that John Doe was working for Agency A while working on Project
17, Task 3 in 2008, but was working for Agency B while working on Project
23, Task 11 in 2009, then you'll need to keep track of Staff/Agency
connections.

If you want to see EVERYTHING John Doe ever touched, but don't care who he
was working for at the time, you don't need the Staff/Agency connection.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"RAK" wrote in message
...


"Jeff Boyce" wrote:

The way you handle many-many relationships in Access is via a junction
table.

It sounds like you are saying that:

One Project can have many Tasks.
One Task can be assigned to (any-of-many) Staff
One Staff can be working for (any-of-many) Agencies

Sounds like:
[Project] table - info specific to a project
[Task] table - Task-specific info, including ProjectID (to which
Project
does this task belong?)
[Agency] table - list of agencies and agency-specific info
[Staff] table - AKA [Employee] table
[AgencyStaff] table - a junction table, showing who worked for which
agency during which time frame
[TaskAssignment] table - a junction table, showing which AgencyStaffID
worked on which TaskID

But you're closer to your situation. Does the above make sense?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"RAK" wrote in message
...
I am trying to create an operational database. We hire temporary staff
through several different employment agencies to complete work on
projects
for various clients. The term of employment is linked to individual
projects
which are time limited and include different assignments within each
project.
Many of the people we use have worked on many different projects at
many
different times including several years. I started out thinking I
needed 5
different tables and needed to use a many-many relationship. Here are
the
5
tables I identified with some types of data:
Employee_Personal (usual type data)
Employment_History (includes Dates hired, Projects assigned at
different
times, employment title per project, performance records; rehire
status)
Agency (related to each employee, # provided for each project;
performance
of employees provided)
Project (Clients, Dates, subjects, teams needed, staff needed)
Accounting [(Hours to complete project-administration pre training,
training, execution and administrative client reporting); (staff cost
factors
computed for number of hours *title pay); (computer cost and needs per
project); space cost and needs per project)]

I started looking at the tables to try to see what relationships I
needed
and if I needed junction tables. I didn't know how to handle the
different
dates of employment and different assignments for each employee and
wondered
if I could use multi-valued fields or value lists for this data. Then
I
started doubting if I was going in the right direction and thought I'd
see
if
I could get any help.



.
Yes, thanks. This is the direction I was heading but got a little lost
when I started working on relational links and defining the foreign keys.
The AgencyStaff junction table appears to take care of the relationships
for staff & agency. But I'm not sure what relationships are defined in
the TaskAssignment junction table. Can I impose upon you for a little
more detail and perhaps a light will shine for me on how to handle the
accounting data.

To clarify your conclusions:
One Project can have many Tasks. Yes
One Task can be assigned to (any-of-many) Staff Yes
One Staff can be working for (any-of-many) Agencies No, not in same
year



  #10  
Old December 16th, 2009, 05:33 PM posted to microsoft.public.access.tablesdbdesign
Rak
external usenet poster
 
Posts: 12
Default when to use many-many relationships or different data bases

To clarify your conclusions:
One Project can have many Tasks. Yes
One Task can be assigned to (any-of-many) Staff Yes
One Staff can be working for (any-of-many) Agencies No, not in same year

Yes, thanks. This is the direction I was heading but got a little lost when
I started working on relational links and defining the foreign keys. The
AgencyStaff junction table appears to take care of the relationships for
staff & agency. But I’m not sure what relationships are defined in the
TaskAssignment junction table. Can I impose upon you for a little more detail
and perhaps a light will shine for me on how to handle the accounting data.
For instance if I have
a staff table and an agency table and a junction staff agency table;
a project table and a task table and a junction project task table;
an accounting table and a junction table for project staff;

does it look like I'm on the right path or did I get lost in the forest?
Can a junction table field be from another junction table or would it be
better to define the fields from individual tables.


"Jeff Boyce" wrote:

The way you handle many-many relationships in Access is via a junction
table.

It sounds like you are saying that:

One Project can have many Tasks.
One Task can be assigned to (any-of-many) Staff
One Staff can be working for (any-of-many) Agencies

Sounds like:
[Project] table - info specific to a project
[Task] table - Task-specific info, including ProjectID (to which Project
does this task belong?)
[Agency] table - list of agencies and agency-specific info
[Staff] table - AKA [Employee] table
[AgencyStaff] table - a junction table, showing who worked for which
agency during which time frame
[TaskAssignment] table - a junction table, showing which AgencyStaffID
worked on which TaskID

But you're closer to your situation. Does the above make sense?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"RAK" wrote in message
...
I am trying to create an operational database. We hire temporary staff
through several different employment agencies to complete work on projects
for various clients. The term of employment is linked to individual
projects
which are time limited and include different assignments within each
project.
Many of the people we use have worked on many different projects at many
different times including several years. I started out thinking I needed 5
different tables and needed to use a many-many relationship. Here are the
5
tables I identified with some types of data:
Employee_Personal (usual type data)
Employment_History (includes Dates hired, Projects assigned at different
times, employment title per project, performance records; rehire status)
Agency (related to each employee, # provided for each project; performance
of employees provided)
Project (Clients, Dates, subjects, teams needed, staff needed)
Accounting [(Hours to complete project-administration pre training,
training, execution and administrative client reporting); (staff cost
factors
computed for number of hours *title pay); (computer cost and needs per
project); space cost and needs per project)]

I started looking at the tables to try to see what relationships I needed
and if I needed junction tables. I didn't know how to handle the different
dates of employment and different assignments for each employee and
wondered
if I could use multi-valued fields or value lists for this data. Then I
started doubting if I was going in the right direction and thought I'd see
if
I could get any help.



.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:36 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 OfficeFrustration.
The comments are property of their posters.