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

ps. i'm using sql 2k. I've posted this question on sqlserver NG some
time ago, but apart from implementation I think I need some more
general design advice