October 23, 2014 at 4:35 pm
Would anyone be willing to comment on this design? I assume this is a common need.
A NULL EndDate would indicate current status.
One issue I see if the difficulty of preventing overlapping ranges to be entered.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Statuses]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[Statuses]
CREATE TABLE [dbo].[Statuses](--Maybe Stati is more proper
[ContactID] [int] NOT NULL,
[ContactStatus] [varchar](20) NOT NULL,
[StartDdate] [date] NOT NULL,
[EndDate] [date] NULL,
CONSTRAINT [PK_Statuses] PRIMARY KEY CLUSTERED
(
[ContactID] ASC,
[ContactStatus] ASC,
[StartDdate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO dbo.Statuses (ContactID,ContactStatus,StartDdate,EndDate) VALUES (1,'Ecstatic','20140101','20140105')
INSERT INTO dbo.Statuses (ContactID,ContactStatus,StartDdate,EndDate) VALUES (1,'Melencholy','20140106','20140110')
INSERT INTO dbo.Statuses (ContactID,ContactStatus,StartDdate,EndDate) VALUES (1,'Bewildered','20140111',NULL)
SELECT * FROM dbo.Statuses
--Select the status for a specific date
SELECT * FROM dbo.Statuses
WHERE
(
StartDdate <= '20140107' AND
EndDate >= '20140107'
)
OR
(
StartDdate <= '20140107' AND
EndDate IS NULL
)
--Select the current status
SELECT * FROM dbo.Statuses
WHERE
EndDate IS NULL
October 24, 2014 at 10:55 am
I like just ommitting the end date. I wonder what cases there are for which this doesn't work well?
October 24, 2014 at 11:45 am
Contracts or price agreements are generally effective for a predetermined time span thus waranting a begin/end date pair. A status, on the other hand, is a state of a row of data as of the point in time of entry - not a span. A future date of a change of status cannot be predicted. You could set an end date on a previous row when a new status occurs, but, be aware you are intentionally denormalizing data to simplify retrievals.
Are you allowed to enter a status for a date in the past, before an existing status?
Can you enter a status for a future date?
Are two statuses allowed to be effective and overlapping?
Must there always be a status for any and all points in time for the life of the row from when it was entered up until now?
Are status rows updatable? This can cause serious inconsistencies.
October 24, 2014 at 2:31 pm
Thanks for you response.
>>Are you allowed to enter a status for a date in the past, before an existing status?
No, but a date range could be split in which the start date would not change., so once a start date is established there will never be a preceding one.
>>Can you enter a status for a future date?
No
>>Are two statuses allowed to be effective and overlapping?
No
>>Must there always be a status for any and all points in time for the life of the row from when it was entered up until now?
Yes
>>Are status rows updatable? This can cause serious inconsistencies.
Yes.
October 25, 2014 at 9:24 am
It looks to me as if it would be easier to avoid strange results if you left out the EndDate column. Since you say tha entering a start date in the past or in the future is not allowed to happen, the end date is redundant and can only add scope for constructing inconsistent data.
edit: some queries will be a little more complex without the end date column. This is a small price to pay for the reduction in complexity of inserts (and of updates and deletes, if there are any).
Tom
October 27, 2014 at 5:27 am
If it is an OLTP database, I suggest you updated the current status in a transaction table and have its trigger write history rows to a statuses table. That way, SELECTs on current status will be easy.
November 4, 2014 at 9:14 am
Thanks all. I will be entering a development and testing phase and I will post back if I have any questions or something to add to the conversation.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply