Recommendation || How should a database object that moves through phases be represented in a database model?

Question

I am new to working on databases and am currently working on a database model for a project management tool. Is there a convention for representing an object that has different phases with different data associated with each phase? What is that convention?

For example - a potential project has fields associated with it such as the inception date, project lead, etc. Once the project is complete it enters the compliance phase which has different fields such as a term date, borrower etc. Would you model these as separate objects where the dependent object inherits the independent objects traits?

Any resources or recommendations are appreciated.

Answer 1

If you have a definite number and order of milestones I would simply create a field for each milestone met date. Then create a view or calculated column to display the current phase state.

SELECT ID, Phase1CompletedDate, Phase2CompletedDate, Phase3CompletedDate, 
CASE WHEN Phase1CompletedDate is null THEN 'Phase 1' 
WHEN Phase2CompletedDate is null then 'Phase 2'  
WHEN Phase3CompletedDate is null then 'Phase 3' 
ELSE 'Complete' END As CurrentPhase
From Table