Modelling DB with separate records for each year

#11

H5N1 wrote:

Quote:

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

why is it bad first choice? Isn't it basis of any normalization?

To answer the second question first: NO.

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

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

Quote:

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

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.

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

Have a nice day, H5.

HTH,
ed

#12

H5N1 wrote:

Quote:

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

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 Order#, 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Modelling DB with separate records for each year

comp.databases comp.databases

Re: Modelling DB with separate records for each year - 05-15-2006 , 01:03 PM

Re: Modelling DB with separate records for each year - 05-15-2006 , 04:36 PM