Hello

I'm designing a DB for HR department to maintain annual budgets. It
should contain the personnel structure and budget items, but only
related to specific year ie. no 2 items with different year values
should be in relationship.

So basically this approach involves - when the fiscal year is ending -
duplicating whole db structure while changing only year attribute, and
then modifying those new records to reflect changes in corporate
structure and in new budget (that's how the client would like it to
work).

The most obvious approach would be to add to nontemporal db structure
table FiscalYear and relate it with all other tables.

Unfortunately it means adding tens of "WHERE FiscalYearID =
@CurrentFiscalYearID" in all queries, or something similar, which seem
not so elegant and error prone.

It feels awkward for me, since for the whole year I would like to be
able to abstract from temporal dimension - current year records are
usually the whole universe for me (except some historical data
reporting).

Does it ring any bell? Could anybody point me in a right direction (if
it exists)? I'd be gratefull for any comments.


The other problem is DB duplication when the year ends - what would be
the recommended way to do that - to duplicate every record in db,
changing only relationship to next FiscalYear record (ms sql server)?


Thanks a lot
H.N.

H5N1 wrote:
You seem to be somewhat confused as what you want/need. You say
that you only want to deal with one year, but you also say that
you sometimes need to deal with historical data.

Assuming you need the historical data you should keep the
information for all of the years you are interested in in a
single table and include a column with the year in each row of
the table.

If you are concerned about the WHERE clause being error prone
use a programming editor that allows you to insert files and
then put your WHERE clause in a file and include it where needed.

HTH

Jerry

you're right, it wasn't too clear - what I meant was that in 95% of all
operations I would be dealing with the current year. So the question
wasn't wether to keep historical data (because I need it fo reports)
but rather how to construct queries in a such way that I would not have
to add WHERE year=@current for each query for each table.

I just thought about other way - I construct something like database
view for each table (but not read-only), some kind of virtual table
(it's just a fuzzy idea) so that it would contain only records from the
current year. When I would add record to this table, it would be
automatically added to it's corresponding 'real' table with current
year value. (Maybe it would be normal table with trigger on update,
updating corresponding table?).

Another option would be to, once a year, create all those auxilliary
tables by copying old values (from last year) to the normal, additional
tables and perform all main queries on them (then I could abstract the
temporal dimension, since they wouldn't containt YEAR column), and,
after fiscal year ends - to copy all the values to main tables, with
YEAR column filled appropriately.

Just to be sure that you get why would I do that - in such case I could
abstract from year value in all queries pertaining budget planning by
using CurrentYear* (i.e. select * FROM CurrentYearBudgetTypes) tables,
and use WHERE only in reporting queries (i.e. select * from BudgetTypes
WHERE year="1996").

What do you guys think about it? May look strange, but isn't it more
convenient and less error prone?


thanks a lot

H5N1 wrote:
So have the "current" year tables. THe previous years data should be in
a Datawarehouse, ie, another set of tables, which includes the "year"
in the key.
A view is better. It puts the "WHERE year=<current year>" where the
programmer doesn't need to think about it.

I think you are copying in the wrong direction.

Well, You proposed about three solutions there, so it is kind of hard
to say which "it" you want us to think about.

Suggestion: the corporate structure does not change automatically,
instantaneously at midnight of newsyears Eve. Think aboiut the
implications of that fact. It should help drive your thoughts toward a
good solution.
Ed

but there's no way to create any kind of "updateable" views, right?

implications of that fact. It should help drive your thoughts toward a
good solution.

True, but budget planning process must capture snapshot of corporate
structure as it is in the beginning of a year, and it can't be changed
later, hence my approach.

H5N1 wrote:
You would always insert into the underlying table. Since you
are dealing with corporate data that won't change you can then
use views to read the data for both current and prior years.
The only catch is that you must remember to redefine the views
at the end of each year.
If your users have any desire to do this year vs last year
comparisons or any trend analysis in the interest of both
performance and simplicity ALL of the data should be in one table.

H5N1 wrote:
I haven't worked on SQL Server. Do they have INSTEAD OF type triggers?
But that view should be updatable (or made updateable). The year value
in the base table is just the current year on any update thru the view.


Ed

thanks

one more thing, then. what would be the easiest way to keep information
about current year? I say year, but rather it would be an ID of some
more general BudgetPeriod table row.

Should I add bool CURRENT column to this table and add constraints to
check, whether only one row has it set to TRUE?

It would mean, that I'd have to add some kind of JOIN to each query
(joining with this BudgetPeriod table), to select only those rows with
Current id...

or maybe it would be better to make a function returning current ID and
use it in WHERE clause? to achieve some kind of global variable...


thanks again

H5N1 wrote:
<PET PEEVE>
WHY DO YOU THINK YOU NEED A PSEUDOKEY FOR A TIME PERIOD??
</PET PEEVE>

Hopefully you have modeled that out. A pseudokey isn't always bad (it's
just a bad first choice in a relational database).

There's really two basic ways to do this. One is generating the budget
summary as needed (ie from the raw data). So your budget table gives
the date range and you compute this on the fly. The other keeps summary
numbers so thay can be reported immediately. I think you can see there
are advantages and disadvantages to both.

yech! Wouldn't the current period be the current period based on
date/time?? This seems a bad choice.

you can find the current budget period like this

SELECT bdgt_id FROM budget
WHERE sysdate BETWEEN budget.start AND budget.end ;

(I'm using ORACLE systedate function. I assume sybase has a similar
function.)

So yes you need a JOIN. Is that really so bad? SYBASE isn't that slow,
is it?

Global variables always remind me of programming in old unstructured
BASIC.
you are welcome,
ed

Ed Prochak wrote:

why is it bad first choice? Isn't it basis of any normalization? I was
just thinking about possibility of adding some additional information a
later bout each time period, but I can do it in seperate table without
any relationships.


yes, bad choice. I was thinking about manual period switching (ie. by
user) when the budget is complete. so, I guess I should place the
information about it in some bool "current" row in time periods table,
with unique constraint on "true" value on that row.