Relationship advice sought

[Niniel]

Hello,

Need to redesign my db, but am having trouble with the following
relationships:

I have a table RSC with RSCID; a table Speakers with SpeakerID; a table
Disclosures with DisclosureID, SpeakerID, ConflictID and DisclosureDate; and
a table RSC-Speakers with RSCID, SpeakerID and DisclosureID. Tables
RSC-Speakers, Speakers and Disclosures are in a kind of circular relationship
right now, but I suspect that's not exactly the right thing to do
[Speakers-[RSC-Speakers]: one-to-many;. Speakers-Disclosures: one-to-many;
Disclosures-[RSC-Speakers]: one-to-many].

I need to model the following: for each RSC, there can be one or more
speaker/s, and each speaker can speak at several different RSCs. That part is
a straightforward many-to-many relationship.
But, every speaker also has to submit disclosures of conflicts of interest,
and for different RSCs there may be different conflicts and therefore
disclosures [by the same speaker]. Regardless, speakers are required to
disclose at least once per year even if nothing changes.

I need to be able to tell for a particular RSC who the speakers were and
when they disclosed what, or if they even provided any information [in a
timely manner, so down the road I'll have to create a report that compares
RSC session dates with disclosure dates].

Help would be greatly appreciated.

 

[Bruce Meneghin]

Niniel,
I think the RSC-Speakers table is not quite right. It should not have a
DisclosureID field. It only establishes the relationship between RSC and
speakers. The Disclosures have a different relationship
I see two scenarios
RSC - Disclosure: many-to-one
RSC - Disclosure: many-to-many
If the relationship is many-to-many, then you would need a RSC-Disclosures
table.
If the relationship is many-to-one, then you would not need that table and
the Disclosure table should have a RSCID field.

 

[Niniel]

Thank you, Bruce.

The disclosures are tied to the speakers, so depending on the number of
speakers, each RSC may indeed have several disclosures associated with it.
And one disclosure may be used for many RSCs, so yes, I guess that would make
it a many-to-many relationship.
But over time, speakers will accumulate several disclosures, and I will need
to be able to tell which particular disclosure a speaker had submitted for a
certain RSC. Which is why I thought I needed to establish a link between RSCs
and disclosures alongside the link between RSCs and speakers.

So maybe I don't need the link between Disclosures and speakers if I have a
link between disclosures and the RSC-Speakers junction table?

 

[Niniel]

I may have stumbled upon a solution... How does this sound?

Instead of linking tables Speakers and RSC in RSC-Speakers, I'll link tables
RSC and Disclosures, which consists of the fields Disclosure ID, SpeakerID
and DisclosureDate. Table Speakers would then be in a one-to-many
relationship with Disclosures, so an RSC could have many disclosures, each of
which could have only one speaker.

 

[Bruce Meneghin]

The circular nature makes it confusing, but here is what I think you need:
table RSC with RSCID;
a table Speakers with SpeakerID;
a table Disclosures with DisclosureID, SpeakerID, ConflictID and
DisclosureDate;
a table RSC-Speakers with RSCID, SpeakerID
a table RSC-Disclosures with RSCID, DisclosureID

You could create a report based on this query with groupings on RSC and
Speaker to get a listing that you speak of

SELECT [RSC-Disclosure].RSCID, Disclosures.SpeakerID,
Disclosures.DisclosureID, Disclosures.ConflictID, Disclosures.DisclosureDate
FROM [RSC-Disclosure]
LEFT JOIN Disclosures ON [RSC-Disclosure].DisclosureID =
Disclosures.DisclosureID;

 

[Niniel]

Yes, that circle is very confusing.
Which is why I thought that maybe turning things around and getting rid of
it might work better. Not sure if it'll work that way, but maybe you could
take a look and comment?
My concern with your idea is that it doesn't seem to establish a connection
between speaker and disclosure in the sense that when I have a new record,
how will I know which disclosure to pick for a speaker?

 

[Bruce Meneghin]

I think I am missing knowledge about how and when this information gets
created. You say "when I have a new record.." In which table??

 

[Niniel]

In table RSC.
I create a record there, and once I pick a speaker, also a record in the
join table/s. The way this is supposed to work is for the user to pick a
speaker name, which then automatically pulls in information about the
disclosures - date and conflicts.

If you want to, you can take a look at my relationship diagram. The join
table is not quite accurate though, the speaker ID has been deleted from it,
and RSCID and DisclosureID will be joint PK once I've put values for
Disclosure ID in there [I only retained the RSCIDs].

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=16187&d=1169847967

:

 

[Bruce Meneghin]

Here's my interpretation of the biz process (**) and accompanying
transactions in the tables I have listed previously

**New RSC is scheduled
add new record to RSC table
**Assign Speakers to RSC
query Speakers to get list of potential speakers(query could also list
disclosures)
select Speakers, creating one record for each in RSC-Speakers table
**Choose Disclosures for newly assigned Speakers
query Disclosures for listing of all Disclosures for each newly
assigned Speaker
select appropriate Disclosures, adding one record to RSC-Disclosures
for each selection

Please correct my interpretation if it is wrong.

The circular nature makes it confusing, but here is what I think you need:
table RSC with RSCID;
a table Speakers with SpeakerID;
a table Disclosures with DisclosureID, SpeakerID, ConflictID and
DisclosureDate;
a table RSC-Speakers with RSCID, SpeakerID
a table RSC-Disclosures with RSCID, DisclosureID

You could create a report based on this query with groupings on RSC and
Speaker to get a listing that you speak of

SELECT [RSC-Disclosure].RSCID, Disclosures.SpeakerID,
Disclosures.DisclosureID, Disclosures.ConflictID, Disclosures.DisclosureDate
FROM [RSC-Disclosure]
LEFT JOIN Disclosures ON [RSC-Disclosure].DisclosureID =
Disclosures.DisclosureID;

Thank you, Bruce.

The disclosures are tied to the speakers, so depending on the number of
speakers, each RSC may indeed have several disclosures associated with it.
And one disclosure may be used for many RSCs, so yes, I guess that would make
it a many-to-many relationship.
But over time, speakers will accumulate several disclosures, and I will need
to be able to tell which particular disclosure a speaker had submitted for a
certain RSC. Which is why I thought I needed to establish a link between RSCs
and disclosures alongside the link between RSCs and speakers.

So maybe I don't need the link between Disclosures and speakers if I have a
link between disclosures and the RSC-Speakers junction table?