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