Best way to duplicate almost whole database (same data for next period scenario)

Hi there

I'm working on a budgeting app which will be used to prepare a budget
for a given period. In the beginning of the next period all data from
the previous one should be duplicated and inserted into a new period so
they would become a base for preparing new data (by updating old
values).

I think first thing should be adding PERIOD column to each table. Or
not each one? For example it wouldn't be necessary to put this column
into intermediary tables, since period value for each row could be
identified by their parent tables. But I'm afraid it could make things
less transparent.


Also, I think that it's more convenient to have natural composite
primary keys for such use instead of single surrogates, since I could
just duplicate each row and just update PERIOD column which would be
part of a composite key (and composite foreign key, hence). But not
always natural keys are possible, so I will have to find a solution to
preserve relationships anyway.

Do you have any suggestions, or could you show me some resources on the
scenario? Should I (in one big transaction) turn off check constraints
(for tables where foreign keys cannot be null), duplicate all tables
with updating the PERIOD column and then update foreign keys basing on
data from previous period (ie. insert into current row's foreign key
column(s) the id of current version of row which was related to
previous version of the current row)?

thanks for any suggestion and sorry for not being too clear.

greets
hp

HP
What is the version are you using?  I think you need read about partition in
the SQL Server 2000/2005


"HP" <ha5en1@gmail.com> wrote in message
news:1164677515.886061.267820@l12g2000cwl.googlegroups.com...
> Hi there
>
> I'm working on a budgeting app which will be used to prepare a budget
> for a given period. In the beginning of the next period all data from
> the previous one should be duplicated and inserted into a new period so
> they would become a base for preparing new data (by updating old
> values).
>
> I think first thing should be adding PERIOD column to each table. Or
> not each one? For example it wouldn't be necessary to put this column
> into intermediary tables, since period value for each row could be
> identified by their parent tables. But I'm afraid it could make things
> less transparent.
>
>
> Also, I think that it's more convenient to have natural composite
> primary keys for such use instead of single surrogates, since I could
> just duplicate each row and just update PERIOD column which would be
> part of a composite key (and composite foreign key, hence). But not
> always natural keys are possible, so I will have to find a solution to
> preserve relationships anyway.
>
> Do you have any suggestions, or could you show me some resources on the
> scenario? Should I (in one big transaction) turn off check constraints
> (for tables where foreign keys cannot be null), duplicate all tables
> with updating the PERIOD column and then update foreign keys basing on
> data from previous period (ie. insert into current row's foreign key
> column(s) the id of current version of row which was related to
> previous version of the current row)?
>
> thanks for any suggestion and sorry for not being too clear.
>
> greets
> hp
>

> What is the version are you using?  I think you need read about partition 
in
> the SQL Server 2000/2005

it's 2k, sorry.

thanks for info, I'm reading about it at the moment. isn't it a
warehose solution? wouldn't it be an overkill for normal db where
performance isn't so important? or is there a way in which partitions
would make abovementioned operations more convenient?

thanks a lot
hp