Hey everyone, I have this question that keeps coming up over and over in terms of best practices.
Lets say I have the following table:
[Process]
----------------
ProcessID int
ProcessName varchar(100)
...
In addition to the fields above, lets say a [Process] can have various states. It can be currently successful, currently unsuccessful, or neither. Theoretically it could be both but we won't worry about that. We also need to know WHEN this state was last updated.
My question is what is the best way to represent this state in the data-structure? I can think of a few possibilities:
(1) Add a column called State (bit, null) that is 1 for successful, 0 for unsuccessful, and NULL for neither. We then add a column LastStateChangeDateTime (datetime, null) that tells us when the State bit was last changed. I've never been given a straight answer as to whether this type of use of a bit is a bad idea.
(2) We add two columns called HasState (bit, not null) and CurrentState (bit, not null) and again our LastStateChangeDateTime (datetime, null) column. This takes away the "tri-state" use of a bit but seems clunky and confusing.
(3) We have two pairs of columns IsCurrentlySuccessful (bit, not null) and SuccessBeginDateTime (datetime, null) along with IsCurrentlyFailing (bit, not null) and FailureBeginDateTime (datetime, null). This makes the two states independent of each other.
(4) No bits, only datetimes: SuccessBeginDateTime (datetime, null) and FailureBeginDateTime (datetime, null). We infer state based on the datetime's themselves. I feel like this will lead to inefficient queries and/or indexes.
I know the above example is contrived, but does anyone have any good solutions as to how to handle this little data-structure problem effectively? Remember that in practice, these "States" are not necessarily mutually exclusive, so it wouldn't be the end of the world if the data-structure allowed for both states at once.
Thank you!