Re: Couple of design questions

Jesse Scott <jscott(at)bmi(dot)net> writes:

> Hello everyone,
>
> I'm just beginning a PHP / PostgreSQL project for someone and there are a
> couple of things where I am not sure the best design for the database.
>
> The first is that we want to store kind of a history of values for the past
> 3 days or so. We want to use this so that we can analyze how the values
> have changed over the last few days. The solution I thought of was having
> 4 columns in the table for each value, like this:
>
> somedata int,
> somedata_24 int,
> somedata_48 int,
> somedata_72 int,
>
> There are 3 different variables that we want to keep a history for in each
> row. So what I thought we could do is at some arbitrary time each day,
> copy the values from somedata into somedata_24, from somedata_24 into
> somedata_48, from somedata_48 into somedata_72, and just forget whatever
> was in somedata_72. My question is, how long would something like this
> take (relatively speaking, I don't know the hardware specs of the server
> exactly, it will be professionally hosted I believe) if there were around
> 20,000 rows? If it would take too long or be too taxing on resources, do
> you have any other ideas on how to handle something like this?
<snip>

Would it not be better to have something like:

uid int, -- identified piece of data
log_date date, -- day the data was logged
data int, -- data to be stored

The primary key will be composed of the uid and the log_data together.
Now there is not need to age the data. If you want data from 24 hours
ago, you take the current date, subtract one day, then do select based
on that. Same with 48 hours, etc. Now all you have to do is
periodically delete data older than a few days. But this method lets
you choose arbitrarily how many days to keep. And if you also index
the data by the log_date, the deleting old data will be very fast.

<snip>

--
Prasanth Kumar
kumar1(at)home(dot)com