The way I see it, the fact that (a) a concrete Task can present distinct States at different points in time entails (b) the creation of a table that is going to contain a time series, as I will detail below.
Business rules
In order to define the structure and constraints of a relational database, it is paramount to first identify and formulate the corresponding business domain rules (i.e., delineate the conceptual schema) with precision, taking into account the relevant entity types, properties and the corresponding interconnections. In this way, some of the formulations that are especially significant in the scenario under consideration are:
- A Task presents one-to-many StateSpecifications
- A Task cannot present more than one StateSpecification at the same Instant
- A StateSpecification is considered Current
- from the particular Instant in which it is entered
- until the exact Instant when the successive
StateSpecification is entered
- A State defines zero-one-or-many StateSpecifications
- A Task receives zero-one-or-many Comments
Consequently, based on the aforementioned formulations, I created the IDEF1X† diagram shown in Figure 1:
As you can see, Task and State are depicted as individual entity types, each with its own group of properties (or attributes) and its own (direct) associations (or relationships), which are expressed via the corresponding verb phrases, lines, cardinalities and FOREIGN KEY (FK) marks.
The entity type denominated StateSpecification, associated with the two brought up before, is a central aspect in the solution I will explain below.
Expository logical design and sample data
I shaped an expository logical design, based on the IDEF1X diagram above, by means of the DDL structure that follows:
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.
-- Also, you should make accurate tests to define the most
-- convenient physical implementation settings; e.g.,
-- a good indexing strategy based on query tendencies.
-- As one would expect, you are free to use your
-- preferred or required naming conventions.
CREATE TABLE Task (
TaskNumber INT NOT NULL,
Description CHAR(90) NOT NULL,
Etcetera CHAR(30) NOT NULL,
CreatedDateTime DATETIME NOT NULL,
--
CONSTRAINT Task_PK PRIMARY KEY (TaskNumber)
);
CREATE TABLE State (
StateCode CHAR(1) NOT NULL,
Name CHAR(30) NOT NULL,
Description CHAR(60) NOT NULL,
--
CONSTRAINT State_PK PRIMARY KEY (StateCode),
CONSTRAINT State_AK1 UNIQUE (Name), -- ALTERNATE KEY.
CONSTRAINT State_AK2 UNIQUE (Description) -- ALTERNATE KEY.
);
CREATE TABLE StateSpecification (
TaskNumber INT NOT NULL,
SpecifiedDateTime DATETIME NOT NULL,
StateCode CHAR(1) NOT NULL,
--
CONSTRAINT StateSpecification_PK PRIMARY KEY (TaskNumber, SpecifiedDateTime), -- Composite PRIMARY KEY.
CONSTRAINT StateSpecification_to_Task_FK FOREIGN KEY (TaskNumber)
REFERENCES Task (TaskNumber),
CONSTRAINT StateSpecification_to_State FOREIGN KEY (StateCode)
REFERENCES State (StateCode)
);
CREATE TABLE TaskComment (
TaskNumber INT NOT NULL,
CreatedDateTime DATETIME NOT NULL,
Body TEXT NOT NULL,
--
CONSTRAINT TaskComment_PK PRIMARY KEY (TaskNumber, CreatedDateTime),
CONSTRAINT TaskComment_to_Task FOREIGN KEY (TaskNumber)
REFERENCES Task (TaskNumber)
);
--
--
The State table
The State table previously illustrated fulfills a look-up role, and is meant to contain five rows (as per the five State samples you provided):
+-—————————-+-—————————-+-———————————————-+
| StateCode | Name | Description |
+-—————————-+-—————————-+-———————————————-+
| O | Open | Indicates that… |
+-----------+-----------+-----------------+
| B | Begun | Indicates that… |
+-----------+-----------+-----------------+
| R | In review | Indicates that… |
+-----------+-----------+-----------------+
| C | Completed | Indicates that… |
+-----------+-----------+-----------------+
Note the definition of a PRIMARY KEY (PK) constraint on a column retaining values that are, at the same time, meaninfgul (regarding interpretation by end users and technicians) and small (in terms of bytes at the physical implementation level), which makes it both readable and fast regarding, e.g., data retrieval.
If new business domain States arise, you can of course INSERT the corresponding rows INTO the State table.
The State Specification table
Now, suposse that Task no. 1750 presents the following State History in the form of (unordered) rows held in the StateSpecification table:
+-——————————-+-———————————————————————-+-—————————-+
| TaskNumber | SpecifiedDateTime | StateCode |
+-——————————-+-———————————————————————-+-—————————-+
| 1750 | 2016-12-01 16:58:12.000 | O |
+------------+-------------------------+-----------+
| 1750 | 2016-12-02 09:12:05.000 | B |
+------------+-------------------------+-----------+
| 1750 | 2016-12-04 10:57:01.000 | R |
+------------+-------------------------+-----------+
| 1750 | 2016-12-07 07:33:08.000 | C |
+------------+-------------------------+-----------+
| 1750 | 2016-12-08 12:12:09.000 | R |
+------------+-------------------------+-----------+
| 1750 | 2016-12-08 19:46:01.000 | B |
+------------+-------------------------+-----------+
| 1750 | 2016-12-09 06:24:07.000 | R |
+------------+-------------------------+-----------+
| 1750 | 2016-12-11 07:24:07.000 | C |
+------------+-------------------------+-----------+
As demonstrated, each value in the SpecifiedDateTime column indicates the precise point in time in which a given Task (via the TaskNumber) started presenting a particular State (by virtue of StateCode).
Seeing that the StateCode column keeps values that have a quite clear intention, it helps to save the usage of a JOIN clause. It is paramount to make clear that this does not imply that JOINs are something detrimental (they are, in fact, fundamental and powerful instruments in any relational database) but, whenever suitable, reducing the consumption of the valuable resources of the system is decidedly practical.
The Comment table
The Comment table, as exemplified above, can easily be related to the Task table via a FK constraint definition, because the PK value of a Task will remain the same, no matter if its State undergoes zero, one or more modifications.
I assume that, in the business context of relevance, a Comment (a) will always depend on the existence of a Task occurrence and (b) will always belong to the same indidivual Task occurrence; therefore, I defined the Comment table with a composite PK that incorporates the PK column of Task and the exact DateTime in which a particular Comment was created. Apart from that, you might like to evaluate the attachment of a column for system-controled surrogates (e.g., a column with the AUTO_INCREMENT attribute in MySQL) to this table, it may or may not offer a better execution performance (probably not), so carrying out some thorough testing sessions would clarify the case (a non-data addition, as is the case of columns holding system-controled surrogates, should always be well assessed and justified).
Data derivation code samples
There are some important data points that will very likely be required with respect to the scenario in question, but they should be obtained as derived (or calculated) values.
For example, if you need to obtain the CurrentState for Task no. 1750, you can derive it with the next SELECT statement:
SELECT T.TaskNumber,
SS.StateCode AS CurrentStateCode,
SS.SpecifiedDateTime
FROM Task T
JOIN StateSpecification SS
ON T.TaskNumber = SS.TaskNumber
WHERE T.TaskNumber = 1750 -- You can provide a parameter instead of a fixed value.
AND SS.SpecifiedDateTime = (
SELECT MAX(SpecifiedDateTime)
FROM StateSpecification InnerSS
WHERE T.TaskNumber = InnerSS.TaskNumber
);
When you face the necessity to get the CurrentState for all Tasks, you can declare a statement like the one that follows:
SELECT T.TaskNumber,
SS.StateCode AS CurrentStateCode,
SS.SpecifiedDateTime
FROM Task T
JOIN StateSpecification SS
ON T.TaskNumber = SS.TaskNumber
WHERE SS.SpecifiedDateTime = (
SELECT MAX(SpecifiedDateTime)
FROM StateSpecification InnerSS
WHERE T.TaskNumber = InnerSS.TaskNumber
);
If you have to derive the StartDate and EndDate of all the StateSpecifications that correspond to Task no. 1750, you may utilize:
SELECT T.TaskNumber,
T.Description,
SS.StateCode,
SS.SpecifiedDateTime AS StartDateTime,
(
SELECT MIN(SpecifiedDateTime)
FROM StateSpecification InnerSS
WHERE T.TaskNumber = InnerSS.TaskNumber
AND InnerSS.SpecifiedDateTime > SS.SpecifiedDateTime
) AS EndDateTime
FROM Task T
JOIN StateSpecification SS
ON T.TaskNumber = SS.TaskNumber
WHERE T.TaskNumber = 1750 -- You can provide a parameter instead of a fixed value.
ORDER BY StartDateTime DESC;
In case you must obtain the StartDate and EndDate of all the StateSpecifications, i.e., the whole period in which they were current:
SELECT T.TaskNumber,
T.Description,
SS.StateCode,
SS.SpecifiedDateTime AS StartDateTime,
(
SELECT MIN(SpecifiedDateTime)
FROM StateSpecification InnerSS
WHERE T.TaskNumber = InnerSS.TaskNumber
AND InnerSS.SpecifiedDateTime > SS.SpecifiedDateTime
) AS EndDateTime
FROM Task T
JOIN StateSpecification SS
ON T.TaskNumber = SS.TaskNumber
ORDER BY StartDateTime DESC;
VIEW definitions
Naturally, one or more of the SELECT statements displayed above can be set up as VIEWs, so that you can arrive at, e.g., the pertinent information directly FROM one single table (a derived one, yes, but it would still be a table).
Demos
I created live demos of the code here deliberated in this SQL Fiddle (running on MySQL 5.6) and in this db<>fiddle (running on Microsoft SQL Server 2014, if someone is interested).
Responses to comments
I think I shall have to go about performing this modification, and think the system will benefit from the additional functionality of the StateSpecification table. It goes to show that good database design can accommodate all our needs without making things messy.
I could not agree more with your second assertion. A database that is designed properly (conceptually, logically and physically), usually becomes a key element in a software development project. If a relational database reflects the business context of interest with accuracy, it can —to be brief— ease its maintenance substantially and offer vast versatility. That is why it is very useful to analyze carefully the structure of the relevant things, to observe them in terms of what they are in the real world, and then represent them with precision in a database implementation.
The data, in and of itself, is a highly valuable organizational asset and, a consequence, it should be administered as such. A reliable manner to achieve said objective is employing technical means that are supported on sound theory, and in the field of data management there is nothing more sound than the relational model. This is in agreement with the Dr. E. F. Codd’s 1981 Turing Award Lecture, entitled Relational Database: A Practical Foundation for Productivity.
Also, just to clarify, the StateSpecification table has to be updated manually on every change of state, correct?
Basically yes, the StateSpecification table has to be brought up to date each time that a Task presents a State change. This procedure should be performed by way of an INSERT operation that introduces a new StateSpecification row that should include a FK value that points to the Task row that applies.
It is opportune to stress that when a Task row is INSERTed, it will have to present a StateSpecification indicating the State deemed as ‘Open’ (by dint of an ‘O’ value retained in the StateCode column), hence you should make use of well defined ACID TRANSACTIONS so that both the Task row and its StateSpecification counterpart are treated as a single unit. In this manner, the two respective INSERT operations can either succeed or fail as a whole, maintaining the involved data consistent with the identified business rules.
Comparable scenario
You might find of help
as it includes a case that has similar features.
Endnotes
† Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) the early theoretical work authored by the originator of the relational model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.
