CODEWITHSUNDEEP
sqlsql-serverdatabase-designrelational-database
Deadeye
Deadeye

Reputation: 123

storing data ranges - effective representation

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.

Upvotes: 0

Views: 173

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

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:

  1. Modify the row in the table with the enddate = 01/01/9999 to have an enddate of yesterday.
  2. 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

Upvotes: 0

DJ.
DJ.

Reputation: 16247

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

Upvotes: 1

Related Questions