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 [EMAIL PROTECTED] ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]] > > 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
