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.
Avoiding pitfalls of a naturally recursive relationship between tables
database design