when to use many-many relationships or different data bases
in [Tables]
|
From: Jeff Boyce on 16 Dec 2009 11:44 As others have pointed out, the "right" way to normalize is something of an art, and requires that you know both the domain you're working in and the output you will need. A "junction" table is just that, a way to resolve the intersection of two entities (approx. tables) that have a many-to-many relationship. Yes, it is possible to have a junction table used to resolve a many-to-many relationship between tables, each of which might be junction tables themselves. 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" <RAK(a)discussions.microsoft.com> wrote in message news:9D450F38-129B-4BE5-BA9D-40EC689C4070(a)microsoft.com... > 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" <RAK(a)discussions.microsoft.com> wrote in message >> news:589C60C8-B28D-4D1C-A00A-19DB5F04B55A(a)microsoft.com... >> >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. >> > >> >> >> . >> |