Oracle-L: Re: Design question: EFFECTIVE_?

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Tue, 23 Apr 2002 11:03:30 -0800
Message-ID: <F001.0044CEED.20020423110330@fatcity.com>

Chris,

Why do you want RATE_EFFECTIVE_END_DATE in your PK? Having it as a part PK will require constant modifications to PK (which is not very good idea).
(RATE_CODE, RATE_EFFECTIVE_START_DATE) should be enough to uniquely identify a record.

I understand, that to get "current" rate you need both "start" and "end" dates, which will lead to additional index (in addition to PK, if "end_date" isn't part of PK). But, it seems to be less evil, than constant modifications of PK.
As for NULLs in RATE_EFFECTIVE_END_DATE, I'd rather assign some date in future (like "01/01/4000") for "current" rate, when new record created (and modify it to "sysdate" when rate becomes "old"). Thus avoiding "IS NULL" when querying this column (where RATE_EFFECTIVE_END_DATE < "01/01/4000" for current rate).

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, April 23, 2002 1:53 PM

> Hi Chris,
>
> I've seen this type of design before. Are you going to be storing
> historical rates? It kind of looks like it.
>
> What I've seen that kills the queries is queries for current rates wanting
> 'and RATE_EFFECTIVE_END_DATE IS NULL'. This was on data that was a subset
> of an airline GDS. (Fairly good sized database with poor design)
>
> If you are not storing historical rate codes, why would you even need the
> end_date? I would think if you are going to need history, you need the
> end_date and I don't think you can get around it.
>
> Just a thought, fwiw
>
> Lisa Koivu
> Oracle Database Administrator
> Fairfield Resorts, Inc.
> 5259 Coconut Creek Parkway
> Ft. Lauderdale, FL, USA 33063
> Office: 954-935-4117
> Cell: 954-309-4157
>
>
>
>
> > -----Original Message-----
> > From: Grabowy, Chris [SMTP:cgrabowy_at_fcg.com]
> > Sent: Monday, April 22, 2002 7:55 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Design question: EFFECTIVE_?_DATE in a RATE table...
> >
> > A design question, my RATE table looks something like this....
> >
> > SQL> desc rate
> > Name
> > Null? Type
>

> > -------- -----------------
> > RATE_CODE
> > NOT NULL CHAR(2)
> > RATE_EFFECTIVE_START_DATE
> > NOT NULL DATE
> > RATE_EFFECTIVE_END_DATE
> > NOT NULL DATE
> > .
> > .
> >
> > those are the PK fields...and the SQL to query the table is easy. I am
> > trying to determine if I can "tighten up" the table, like this...
> >
> > SQL> desc rate
> > Name
> > Null? Type
>

> > -------- -----------------
> > RATE_CODE
> > NOT NULL CHAR(2)
> > RATE_EFFECTIVE_DATE
> > NOT NULL DATE
> >
> > I just haven't been able to code the right SQL.
> >
> > Has anyone else taken this approach?
> >
> > Sorry for these design questions, I am just being very anal about every
> > table, PK column and index, before we dive into development and
everything
> > becomes "hard coded".
> >
> > TIA!!!!
> >
> > Chris
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Grabowy, Chris
> > INET: cgrabowy_at_fcg.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Koivu, Lisa
> INET: lisa.koivu_at_efairfield.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Apr 23 2002 - 14:03:30 CDT

Re: Design question: EFFECTIVE_?_DATE in a RATE table...