Many to many relationship issue

[lcc]

Hi
I have been racking my brain and cannot find a solution, I am sure its
simple but cant seem to find the answer, I have searched previous
posts, tried various relationship linking scenarios, many to many
relationship join table, but cant figure how to link correctly or if
my current table set up is incorrect

I have created an Issues database which for each issue has
the issue (issues_t),
which can have several actions relating to the issue (Actions_t)
and then several update notes(updates_t) for each of those actions

this side of the tables/relationship is fine

My problem lies with the staff relationships. For each issue there is
an owner, accountable and delivery person, (could be the same people)
and then for the action there is a separate delivery person.


Tables

Issues(t) - contains the issue details
SIS ID - Primary key(auto number)
Description
Accountable (lookup from contacts table)
Delivery (lookup from contacts table)
Owner (lookup from contacts table)
Plus other fields

Actions(t)
ActionID - primary key (number)
ActionDesc
AssignedTo (lookup from contacts table)
Plus other fields

Contacts(t) - contains the staff details
ID -Primary key (number)
Name etc

Updates(t)
updateID-primary key (number)
comments

can you please advise the best way to link the contacts to the various
accountable, owner etc fields

Thanks

 

[Destin Richter]

LCC - from what you describe, I'd have a field in Actions table that includes
the SIS ID (primary field of the Issues table) so that on my Issues form,
there would be a subform that included unlimited related Actions (or, in lieu
of a subform, at least a command button to pop up the Actions forms showing
all records that include the related SIS ID). The relationships work
themselves out fine if you imagine how your form will look, how users will
use your database to record the issues, possible solutions, etc. Feel free
to get in touch. If you haven't developed your forms yet, I'll do a quick
one for you and email it over.

Destin
(e-mail address removed)

 

[gllincoln]

Hi!

Two 'one tables'

Issues & Contacts.

Be sure that there can be only one value to each field in these two tables.

Accountable (lookup from contacts table)
Delivery (lookup from contacts table)
Owner (lookup from contacts table)
Plus other fields

If there can be more than one accountable, or delivery or owner or any of the plus other fields - then they belong in one of the transaction tables. (Actions/Updates)

I'm rather suspicious of having the delivery person field in the Issues table based on your description. Probably doesn't belong there. Accountable is a maybe. How are you going to handle escalated issues? Where the accountable person moves the issue up the food chain to his supervisor? If the accountable person is in the Issues table, then that escalation probably demands that you create a 'new issue' at this point. You don't really want to overwrite that field and lose the history do you?

Not absolutely sure who the owner represents here - if it is the client/the person who is experiencing the issue - then that's pretty stable, one to one with the issue.

I think the accountable person may belong in the action table and the delivery person entry probably belongs in the updates table.

I see the process flowing this way.

Issues ----
|------>Actions ------>Updates
Contacts---

Action carries the foreign keys (primary key of issue)(primary key of accountable person)
Updates carries the foreign keys (primary key of action) (primary key of delivery person)

It doesn't seem to me that you need to reference Issues directly from Updates - Action is clearly the joining entity here.

Ah well - not knowing most of the specifics, that's about as far as I can get.

Hope this helps,
Gordon

 

[lcc]

Hi!

Two 'one tables'

Issues & Contacts.

Be sure that there can be only one value to each field in these two tables..


If there can be more than one accountable, or delivery or owner or any of the plus other fields - then they belong in one of the transaction tables. (Actions/Updates)

I'm rather suspicious of having the delivery person field in the Issues table based on your description. Probably doesn't belong there. Accountable isa maybe. How are you going to handle escalated issues? Where the accountable person moves the issue up the food chain to his supervisor? If the accountable person is in the Issues table, then that escalation probably demands that you create a 'new issue' at this point. You don't really want to overwrite that field and lose the history do you?

Not absolutely sure who the owner represents here - if it is the client/the person who is experiencing the issue - then that's pretty stable, one to one with the issue.

I think the accountable person may belong in the action table and the delivery person entry probably belongs in the updates table.

I see the process flowing this way.

Issues ----
          |------>Actions ------>Updates
Contacts---

Action carries the foreign keys (primary key of issue)(primary key of accountable person)
Updates carries the foreign keys (primary key of action) (primary key of delivery person)

It doesn't seem to me that you need to reference Issues directly from Updates - Action is clearly the joining entity here.

Ah well - not knowing most of the specifics, that's about as far as I can get.

Hope this helps,
Gordon













- Show quoted text -

Hi and thanks for your observations, logically the database would
work how you state, but the requirements are different as they are
trying to capture the 3 owners of the overall issue from different
sources

Let me try to explain in another way

The way this works is that there is the overall issue that has the
following people attached to it, all 3 fields would be filled in, and
they could be the same person

Accountable
Delivery
Owner

from each overall Issue various ACTION's are derived, and each one of
these actions would have a person who is responsible for delivering on
that action (AssignedTo) (keeping history is not really an issue)

then for each action there is an update on the status of progress (no
one responsible)

I summarise an example

Issue 87 - Database contains old customer records and old SQL code
(Act/Del/Own)
action 1 - fix sql code (assigned to LC)
update 1 - meeting held to discuss approach
update 2 - Work in progress with IT


action 2 - define duplicate data (assigned to HB)
update 1 - Jane talking to TB re etc etc


hope this explains things a bit better, but as you see there is a
reason for having the 3 fields in the issues table (or against the
issue itself)

As stated the issue-actions-updates is working fine, and if i only had
my assignedto link to the contacts, but its linking the 3 fields in
the issues table that is my problem

Happy to send copy of DB if easier to understand layout

 

[lcc]

LCC - from what you describe, I'd have a field in Actions table that includes
the SIS ID (primary field of the Issues table) so that on my Issues form,
there would be a subform that included unlimited related Actions (or, in lieu
of a subform, at least a command button to pop up the Actions forms showing
all records that include the related SIS ID).  The relationships work
themselves out fine if you imagine how your form will look, how users will
use your database to record the issues, possible solutions, etc.  Feel free
to get in touch.  If you haven't developed your forms yet, I'll do a quick
one for you and email it over.

Destin
(e-mail address removed)













- Show quoted text -

Hi D

I have the SIS Id in the actions table, and my form, subform etc is
wrking fine, (mnaged to get than one working with no problems. The
problem lies with linking the contacts to the Issues(t) - Acctoutnable/
delivery/owner fields and Action(t) assignedTo field