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:
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.

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

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.
--
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.

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.

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.

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 * FRM 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:
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.
--
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.
--
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.

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.

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.

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?).

A view is better. It puts the "WHERE year=<current year>" where the
programmer doesn't need to think about it.

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.

I think you are copying in the wrong direction.

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 * FRM 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

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

A view is better. It puts the "WHERE year=<current year>" where the
programmer doesn't need to think about it.

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

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.

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:
>>A view is better. It puts the "WHERE year=<current year>" where the
>>programmer doesn't need to think about it.
>
>
but there's no way to create any kind of "updateable" views, right?

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.
>
>
>>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.

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.

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:
A view is better. It puts the "WHERE year=<current year>" where the
programmer doesn't need to think about it.

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

I haven't worked on SQL Server. Do they have INSTEAD F 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 JIN 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:
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.

<PET PEEVE>
WHY D YU THINK YU NEED A PSEUDKEY FR A TIME PERID?
</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. 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.

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

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

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

you can find the current budget period like this

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

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

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

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

Global variables always remind me of programming in old unstructured
BASIC.
--
thanks again

you are welcome,
ed

Ed Prochak wrote:
H5N1 wrote:
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.

<PET PEEVE>
WHY D YU THINK YU NEED A PSEUDKEY FR A TIME PERID?
</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).

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.

There's really two basic ways to do this. 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.
--
Should I add bool CURRENT column to this table and add constraints to
check, whether only one row has it set to TRUE?

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

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.

H5N1 wrote:
Ed Prochak wrote:
H5N1 wrote:
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.

<PET PEEVE>
WHY D YU THINK YU NEED A PSEUDKEY FR A TIME PERID?
</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).
--
why is it bad first choice? Isn't it basis of any normalization?

To answer the second question first: N

IMH, pseudokeys are used way too much. They are not inherently bad.

Pseudokeys might be introduced to solve problems with complex natural
keys. For example a person can be uniquely identified by a legal name
and the place and time of their birth. But obtaining and entering that
information every time can be error prone, and so a pseudokey, SSN was
created. It had nothing to do with normalizing the database (SSN
predates Codd).

The rules for noralization are guidelines, not physical laws. Similarly
choosing to use a pseudokey is ultimately up to the DB designer. But
where a simple natural key exists, a pseudokey introduces more
potential problems than it saves.

If your budget year runs July1 thru June30 and all the transactions
have a timestamp, what is a budget timestamp ID (BTID) really buying
you? Each transaction that belongs in a given budget year can be
identified by its timestamp. How would you assign BTID to each event?
Would you eliminate the timestamp on the individual tansaction?

Would you have a table something like this?
<other transaction data>
BTID

So you do not know when the transaction happened, at least not any
deeper than which budget year.

would you make the table like this?
<other transaction data>
BTID
transaction_datetime

so you have redundant data to the extent that BTID really represents a
range of dates.

But really my point was, don't blindly add ID columns. They may not be
the correct solution to your problem.

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.
--
There's really two basic ways to do this. 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.
--
Should I add bool CURRENT column to this table and add constraints to
check, whether only one row has it set to TRUE?

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

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.

, I think I can see where you are going with that. It can simplify
some application logic.

Have a nice day, H5.

HTH,
ed

H5N1 wrote:
Ed Prochak wrote:
H5N1 wrote:
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.

<PET PEEVE>
WHY D YU THINK YU NEED A PSEUDKEY FR A TIME PERID?
</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).
--
why is it bad first choice? Isn't it basis of any normalization?

I'm not picking on you, H5, but, I wanted to double check myself. I
was worried I really had this wrong about pseudoKeys, So I've just
been scanning several pages about Normalization from a google search.
In none of them did I find an example of creating a pseudoKey in the
steps for normalizing tables. Several example started with such keys:
Student ID#, Sales #, SSN. But none added new ones during
normalization.

So it does just seem to be a common practice. Like I said it can be
useful. But overuse of ID columns especially as the Primary key seems
to me bad practice.

Ed