-1

I have a database that has 5 1:1 relationships. I have a table called SoftwareVersion. Each SoftwareVersion has 5 phases of certification. The PhaseStatus names are the same for all 5 phases. I could not enforce referential integrity with one PhaseStatus table and a Ph0Status, Ph1Status, etc, field in the SoftwareVersion table. So I built 5 join tables. Now I have 5 1:1 Relationships. Every SoftwareVersion has multiple phases that have a status, but each software version only has one Phase 0 status. Is this a good example of a legitimate 1:1 relationship or is there a better way to build this?

1Ummyeah
  • 1

1 Answers1

0

Every SoftwareVersion has multiple phases that have a status, but each software version only has one Phase 0 status.

I think you mean every SoftwareVersion has many phases, and each phase has one or more possible statuses at a time.

If so, from what I see you need only two tables. You need a PhaseStatus table of all allowable combinations of {phase,status}. Use a FK reference to that table as a constraint to make sure a SofwareVersion in any given phase has a valid status. You might also want tables that enumerate the domains of phases and statuses, in case they have other interesting properties, such as descriptions or perhaps time constraints.

James K. Lowden
  • 7,574
  • 1
  • 16
  • 31
  • So the only issue I have with this is that if I need to ad a new phase status, rather than add one record to the phase status table I would then need to add 5 records with duplicate information. – 1Ummyeah Mar 19 '20 at 18:08
  • Similar, but not "duplicate". A new Phase has its own set of valid statuses that need to be defined. Those rows are different from all other rows; they're not duplicates. Also not a big deal, because how often will you add a phase, and how many statuses could it have? Probably a couple of SQL statements and you're done. If you wanted to, though -- if you want to *require* that two phases allow exactly the same set of statuses -- you could create a StatusGroup table keyed by a StatusGroupId, and use that as a FK in a Phases table instead of a single PhaseStatus table. – James K. Lowden Mar 20 '20 at 19:16