Multiple junction tables between two tables (or just one with multiple fields)?

[Sam_NY]

Hello,



I'm new to the forum so hope I'm posting in the right place.

I'm designing a database to track legislators' support of proposed laws, so I have two main tables: Legislator and Bill

My question has to do with the relationship between the two. It makes sense to have junction table to establish the many-to-many relationships (legislators can support multiple bills and bills ideally have multiple supporters).

The snag is that legislators can support bills in different ways at different points in time. The main actions I'm interest in tracking are: sponsorships (when a legislator supports it before a vote, votes, and votes to override a veto.

My question is: does it make more sense to (A) have a single Legislator_Bill junction table that has different fields for sponsorship, votes, and override votes OR (B) create three SEPARATE junction tables: one for sponsorships, one for votes, and one for override votes?

And I guess option (C) is to have a single junction table into which I would enter each action (sponsorship/vote/override) as a separate line/row, rather than a single one. This sort of makes sense to me (so that I can query for just these if I needed to, but what might be the pro/cons?

I know this is sort of an involved scenario, so thanks to anyone willing to help me think through the design implications.

Sam

[CJ_London]

Option C is the way to go

Option A is not flexible enough (you mention different points in time and what happens if there is another form of support?)
Option B is a big no no - you'll end up with a big query with left joins all over the place,

[Sam_NY]

Thanks, Ajax. That seems to make sense. And I can use a combo box in one field for the action type (sponsor, vote, veto override), adding to it as necessary.

Sam