I am looking for some best practices regarding table design when an entity is expected to evolve over time and get more properties.
As an example let's consider a
process that has a bunch of metadata and can either be running or finished. A running process would have a
startTime but no
endTime, whereas a finished process needs to have an
endTime. It is assumed that every
runningProcess will be finished at some point.
I see some general approaches:
- A single table
processeswith a nullable
endTimecolumn where all processes are stored
- Two tables
finishedProcessesthat look the same except for the
endTimecolumn that the
- An extra table
processEndTimesthat only stores a
processIdwith a FK constraint and an
Option 1) is pretty simple but has the disadvantage that a query for
finishedProcesses would run on a larger table and filter by the
Option 2) has the disadvantage that ending a process is not an atomic action any more but needs to be a transaction to delete a row from
runningProcesses and insert a row in
finishedProcesses. Additionally, a query for all processes with some metadata would always run on a union of the tables.
Option 3) seems to overcome the disadvantages of Option 1) and 2) but has the disadvantage that the table design does not reflect the way I'd model the processes in business logic.
What option would you go for? And why?