I'm developing a system to manage "cases". There will eventually be many different "kinds" of cases, so I've started with a "CaseTemplate" table that can be used to define what a case looks like and, via satellite tables, define the specific characteristics for a particluar kind of case.

One such characteristic is phases. A case may move through a series of phases, but the phases depend on the kind of case. So, naturally, I created a CasePhases table with a many to one relationship with the CaseTemplate table (since each template may define its own phases).

But then I hit a snag... which phase should a case start in? My first thought was that I could just define the "StartingPhase" in the CaseTemplate table using a foreign key, but that creates a recursive relationship between the CaseTemplate and CasePhases tables.

I suppose I could make the StartingPhase field nullable and just not assign a value at first (updating the record later when the phases for the CaseTemplate have been defined, and using business logic to ensure that a CaseTemplate has defined a StartingPhase). Or I could add an "IsStartingPhase" field to the CasePhases table (with some business logic to enforce that a Template may have only one phase set as a Starting Phase). Both have some advantages and disadvantages, but neither strike me as particularly elegant or perferable over the other.

I've got to believe that this is not an uncommon scenario. Are there any best pratices for handling this database relationship, maybe some terminology I'm missing? I'm looking for an explanation of the problem domain I'm dealing with and perhaps some approaches I've not considered.

I'm using a relational database (SQL Server), so solutions using "exotic" technologies probably won't work in my environment.

  • Is it a recursive relationship or just a table linked to itself? – JeffO Feb 26 '14 at 18:04
  • CasePhases is a child table (many to one) of the CaseTemplate table. Now I need to define a relationship between the CaseTemplate and one of the child CasePhases. – JDB still remembers Monica Feb 26 '14 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.