Relationship advice sought (1 Viewer)

[Niniel]

Hello,

I need to re-design my db but it's a bit tricky, for me anyway.
Attached you'll find a screenshot of the relationship window for illustration. The part that I need help with is the relationships between RSCs, speakers and disclosures in the top right part of the window.
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].

That circular relationship I came up with doesn't feel quite right, but I'm not sure how to do it differently.
Help would be greatly appreciated.

 

[The_Doc_Man]

I am not allowed by my site's rules to download things from a non military site so I can't look at your design.

Here is what I see from your words.

RSC to speaker - many-to-many with a junction table. (Note: I presume RSC has dates? If not, date needs to be in the junction table.)

Speaker to disclosure - one-to-many with a check-box in disclosure that says "RSC" - and if not checked, it was simply time for a yearly disclosure. The date must always be in the disclosure since there is a chance that the disclosure was date-forced rather than RSC forced. It would be OK to have a foreign key in disclosure that points back to RSC (in the case where the disclosure was forced by RSC rather than by date). However, this is a case where a join between the RSC pointer and the disclosure would not match the count of all disclosures. Therefore, to see ALL disclosures, a query would have to be based on the disclosure table OUTER JOIN to the PSC table. (Or to a query of disclosure JOIN speaker which is then OUTER JOIN with PSC, a two-stage JOIN).

 

[Niniel]

Ah, too bad; I figured a picture would work better than me trying to explain things.

So let me try to describe my setup in more detail then:

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]

And yes, there is a date associated with RSCs [SessionDate], and I have that in the RSC table. Why should that be in the junction table [RSC-Speakers]?

 

[Niniel]

What if I deleted the direct link between speakers and disclosures?
Then all I'd have was a relationship between disclosures and the join table RSC-Speakers, and the connection between disclosures and speakers would be indirect, via this table.
I believe this system would be able to show existing data accurately, but data entry would be a pain - when I pick a speaker I want the most recent disclosure information to come up automatically.

RSCID - SpeakerID - DisclosureID
1 1 1
1 2 2
2 1 3

 

[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.

See screenshot.

 

[dsigner]

Is a disclosure (or the conflicting interest causing it) time limited?
In other words does it have a possible expiry date or is it for ever once disclosed?

 

[Niniel]

It is valid for a maximum of 12 months, although sometimes new ones are submitted before that.

 

[dsigner]

My thought is that the disclosure is almost a specific one time thing (if most events are annual). It depends on who gets to see the disclosures. It could be unfortunare to release an expired disclosure. If there is an issue here then I would be tempted to make disclosures one time and accept a degree of redundancy in the design. Or they could come from a master list of disclosures for each speaker and attaching them to an event would be a multi choice combi box. There are too many other considerations in the wings to be confident of the best answer.

 

[Niniel]

No, the events, or programs, are run several times a year, so the disclosures are not one-time affairs.
I think what I've come up with works; I just need to figure out a way to limit the selection of available disclosures to the most recent one when entering new programs

 

[dsigner]

Store the expiry date and filter on that. Date less than event date +1 should do it.

 

[Niniel]

All right, thank you.