Posted by Erland Sommarskog on 12/17/06 23:21

Matik (marzec@sauron.xo.pl) writes:
> With shifting data into history, I've ment, that by each change of data
> in primary table, whole record should be copied to the history table
> (nearly same construction as primary table).
> This is than an issue with the second table, storing semicolon
> separated field in one column (splitted) in different table. This need
> to be shifted then also, to a second historical table.

I'm not sure that I see the problem. With a regular design, you would
have two tables for current data, and two tables for historical data.

> Of course, I could ommit using 'working' table, and have only history,
> with inserts, and having a primary table containing a pointer to last -
> newest record as my primary table, to get the newest record.
> The problem is, I'm afraid a little of performance, sice there is all
> other actions done on the primary table (select, searches aso.)

Like Ed said, get the design right first, and do performance tuning
when everything else is working. But some basic ideas for performance
are good when designing for performance. For instance no repeating
groups (i.e. semicolon-separated lists.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]