storing data ranges - effective representation

Question

I need to store values for every day in timeline, i.e. every user of database should has status assigned for every day, like this:

from 1.1.2000 to 28.05.2011 - status 1
from 29.05.2011 to 30.01.2012 - status 3
from 1.2.2012 to infinity - status 4

Each day should have only one status assigned, and last status is not ending (until another one is given). My question is what is effective representation in sql database? Obvious solution is to create row for each change (with the last day the status is assigned in each range), like this:

uptodate     status
28.05.2011   status 1
30.01.2012   status 3
01.01.9999   status 4

this has many problems - if i would want to add another range, say from 15.02.2012, i would need to alter last row too:

uptodate     status
28.05.2011   status 1
30.01.2012   status 3
14.02.2012   status 4
01.01.9999   status 8

and it requires lots of checking to make sure there is no overlapping and errors, especially if someone wants to modify ranges in the middle of the list - inserting a new status from 29.01.2012 to 10.02.2012 is hard to implement (it would require data ranges of status 3 and status 4 to shrink accordingly to make space for new status). Is there any better solution?

i thought about completly other solution, like storing each day status in separate row - so there will be row for every day in timeline. This would make it easy to update - simply enter new status for rows with date between start and end. Of course this would generate big amount of needless data, so it's bad solution, but is coherent and easy to manage. I was wondering if there is something in between, but i guess not.

more context: i want moderator to be able to assign status freely to any dates, and edit it if he would need to. But most often moderator will be adding new status data ranges at the end. I don't really need the last status. After moderator finishes editing whole month time, I need to generate raport based on status on each day in that month. But anytime moderator may want to edit data months ago (which would be reflected on updated raports), and he can put one status for i.e. one year in advance.

@Howard i thought about it, but this does not solve my problem. Uptodate seeems a little easier to manage. — Deadeye, May 28 '12 at 15:58
If most of the status changes are occurring at the "present" end of the timeline, I'd be inclined to agree with @Howard. That avoids the work of updating the end-of-time row frequently. As for changing history, you're stuck with going in and fiddling with one or more rows. Note that, lacking a placeholder for both ends of time, you haven't actually represented your first example row. If you add a status 42 for 12.11.1999 to 11.12.1999 how do you propose to handle the 1.1.2000 date you haven't stored? Or any other gaps in the continuum? `DateQuantizedTimeOffset`? — HABO, May 28 '12 at 16:18
@user92546 first example row is simply start of timeline - the date that won't be considered since start date is each user register time (and from that time date is counted). And first row status is from registered date to uptodate. in general i thought of completly differ solution (if any) - check added info in main question. — Deadeye, May 28 '12 at 16:30
Let me get this straight: you only know statuses after they've occurred? And I would join Howard: from-dates are in my experience easier, and would require no extra rows (the uptodate 01.01.9999 is switched for the fromdate which is the register time). No extra data, less fuss. — Wrikken, May 28 '12 at 16:53
@Wrikken - after consideration, from-dates does seems easier, but still they don't solve primary concern of managing lots of them (and all the additional logic needed). Statuses is something like "planning table", it can be edited on advance as a plan, or after status change as history. And after insert it needs to be editable freely if there are any changes required. — Deadeye, May 28 '12 at 22:51

score 1 · Answer 1 · answered May 28 '12 at 16:52

1

You seem to want to use this table for two things - recording the current status and the history of status changes. You should separate the current status out and move it up to the parent (just like the registered date)

User
===============
Registered Date
Current Status

Status History
===============
Uptodate 
Status

answered May 28 '12 at 16:52

DJ.

16,045
3
42
46

i don't really need last status - i updated question with more context how that data will be used. – Deadeye May 28 '12 at 22:49

score 0 · Answer 2 · answered May 28 '12 at 23:07

Your table structure should include the effective and end dates of the status period. This effectively "tiles" the statuses into groups that don't overlap. The last row should have a dummy end date (as you have above) or NULL. Using a value instead of NULL is useful if you have indexes on the end date.

With this structure, to get the status on any given date, you use the query:

select *
from t
where <date> between effdate and enddate

To add a new status at the end of the period requires two changes:

Modify the row in the table with the enddate = 01/01/9999 to have an enddate of yesterday.
Insert a new row with the effdate of today and an enddate of 01/01/9999

I would wrap this in a stored procedure.

To change a status on one date in the past requires splitting one of the historical records in two. Multiple dates may require changing multiple records.

If you have a date range, you can get all tiles that overlap a given time period with the query:

select *
from t
where <periodstart> <= enddate and <periodend> >= effdate

storing data ranges - effective representation

2 Answers2