RE: Design question: EFFECTIVE_?_DATE in a RATE table...
Chris, The only issue I see with the modified table is that when you wish to obtain history from your child table based on two dates, to determine which rate to apply you need to base your SQL on two records. However if you store starting and ending dates it will be easy to identify the applicable rate. HTH! Aleem -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: Abdul Aleem 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).
RE: RE: Design question: EFFECTIVE_?_DATE in a RATE table...
My first move was to say that I fully agree with Aleem, but on second thoughts I think that the problem for current and historical values is more or less the same. Basically you can store either start and end dates, which may look a bit redundant since the end date of a row is likely to be, give or take one, the start date for another row refering to the same code, or you can store, as you suggest, a mandatory RATE_EFFECTIVE_DATE, or you can also store a RATE_END_DATE which, as some have suggested, should be a date in a distant future rather than NULL for the current rate. Whether you are interested in the current or an historical value is fairly easy, it requires finding either the maximum RATE_EFFECTIVE_DATE below the date of interest for a given code - or the minimum RATE_END_DATE above the date of interest. Note however that in both cases you will need either a subquery or an inline view, which will probably be less efficient than a BETWEEN involving the date, but not dram! ! atically so assuming the proper indices. Indices the size of which will be reduced, by the way, which is good for fast and even range index scans. The code could look like : SELECT VALUE FROM RATE WHERE RATE_CODE = some_code AND RATE_EFFECTIVE_DATE = (SELECT MAX(RATE_EFFECTIVE_RATE) FROM RATE WHERE RATE_CODE = some_code AND RATE_EFFECTIVE_DATE = your_date) You should also try a join with an inline view doing a GROUP BY on RATE_CODE. In some circumstances it could be better. If you intend to use the value at a large number of places, I suggest you store it to a packaged variable to minimize the number of queries. - Original Message - From: Abdul Aleem [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 24 Apr 2002 02:23:25 Chris, The only issue I see with the modified table is that when you wish to obtain history from your child table based on two dates, to determine which rate to apply you need to base your SQL on two records. However if you store starting and ending dates it will be easy to identify the applicable rate. HTH! Aleem -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: Abdul Aleem 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).
RE: Design question: EFFECTIVE_?_DATE in a RATE table...
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).
Re: Design question: EFFECTIVE_?_DATE in a RATE table...
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).
Re: Design question: EFFECTIVE_?_DATE in a RATE table...
Chris,
Yes, I've done it with a single date column as you specified in the second
approach.
The following bit of code may be helpful:
declare
v_claim_date date := to_date('02/01/2002','mm/dd/');
v_rate_code rate.rate_code%type := 'ABC';
v_rate_date date;
begin
select min(effective_date) into v_rate_date
from rate
where rate_code = v_rate_code
and effective_date between v_claim_date and
to_date('12/31/','mm/dd/');
end;
/
Returning no row indicates that the claim is not covered.
This SQL is probably not optimal, but illustrates the idea.
It's much easier for users and developers to juggle a single
date per record than try to ensure the the start_dates and end_dates
are all in sync.
Jared
Grabowy, Chris [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
04/22/2002 04:54 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
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:
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).
