Posted by rottytooth on 10/21/05 00:09

A general data design question:

We have data which changes every week. We had considered seperating
historical records and current records into two different tables with
the same columns, but thought it might be simpler to have them all
together in one table and just add a WeekID int column to indicate
which week it represents (and perhaps an isCurrent bit column to make
querying easier). We have a number of tables like this, holding weekly
data, and we'll have to query for historical data often, but only back
through the last year -- we have historical data going back to 1998 or
so which we'll rarely if ever look at.

Is the all-in-one-table approach better or the seperation of current
and historical data? Will there be a performance hit to organizing data
this way? I don't think the extra columns will make querying too much
more awkward, but is there anything I'm overlooking in this?

Thanks.

[Back to original message]