RE: Design question, historic and views

[Stephane Paquette]

The message did not generate many feedbacks.
Any feedback is welcome on the  historic and views questions.

Thanks

Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 7470 and (514) 925-7187
[EMAIL PROTECTED] 





-Original Message-
Sent: Tuesday, August 19, 2003 10:37 AM
To: [EMAIL PROTECTED]


Hi,

In an ODS, it is feed in near real time during the day and pushes data to
the DW and other systems at night.
So it may need to keep several copies of the same data (several changes or
the DW can be offline so the the ODS may need to keep several days).

All tables have a surrogated PK and the PK from the source systems are used
as alternate non-unique keys.
All tables will have a DW_transfer indicator (yes/no)
There are 2 designs I can immediately think of :

1. I keep all the data in the same table and I use fields like
last_version_indicator (yes/no), start date, end date.
   This is fast since it implies inserting the new record and updating the
previous record.

2. The last version for each rows are kept in a table and the historic are
kept in a second table.Each table has its historic table. This implies
inserting in the core table, insert the previous record in the historic
table and deleting it.

3. another way ?

The ODS must answer the following:
All changes need to be transferred to the DW not only the last version.
End-users will query the ODS (I do not know yet if queries will access only
the last version or not).


Views.
In the case the solution1 is used. Would you used views to do the following
table_t : the table
table_last_version_v : view showing only the last version (with where
last_version_indicator='YES'), used for queries.


In both cases, would you used a view
table_not_transferred_v : view showing records with the
DW_transfer_indicator set to no, that view would be updated by the ETL
processes

or let the the programs do the where DW_transfer_indicator=no.


Thanks

Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 7470 and (514) 925-7187
[EMAIL PROTECTED] 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Paquette
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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, historic and views

[DENNIS WILLIAMS]

Stephane
   Have you tried this one? It averages about 5 messages a day.
For help with list commands, send a message
to  with the
word "help" in the body of the message.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Tuesday, August 19, 2003 11:14 AM
To: Multiple recipients of list ORACLE-L


It'a Bill Inmon type ;) (everything is normalized third normal form).

The DW will probably want a mix of last version/all versions.
Reporting or auditing may need to view all versions.

What DW list, I used to be on 2 of them but not enough trafic.


Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 7470 and (514) 925-7187
[EMAIL PROTECTED] 



-Original Message-
DENNIS WILLIAMS
Sent: Tuesday, August 19, 2003 11:55 AM
To: Multiple recipients of list ORACLE-L


Stephane
   What type of DW is this? Is a simple transaction system or is it more
complex? What I'm asking is whether the individual changes are important or
if only the last value is what you want stored. Actually, there is a good,
active data warehousing list that would probably give you better answers.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Tuesday, August 19, 2003 10:34 AM
To: Multiple recipients of list ORACLE-L


Hi,

In an ODS, it is feed in near real time during the day and pushes data to
the DW and other systems at night.
So it may need to keep several copies of the same data (several changes or
the DW can be offline so the the ODS may need to keep several days).

All tables have a surrogated PK and the PK from the source systems are used
as alternate non-unique keys.
All tables will have a DW_transfer indicator (yes/no)
There are 2 designs I can immediately think of :

1. I keep all the data in the same table and I use fields like
last_version_indicator (yes/no), start date, end date.
   This is fast since it implies inserting the new record and updating the
previous record.

2. The last version for each rows are kept in a table and the historic are
kept in a second table.Each table has its historic table. This implies
inserting in the core table, insert the previous record in the historic
table and deleting it.

3. another way ?

The ODS must answer the following:
All changes need to be transferred to the DW not only the last version.
End-users will query the ODS (I do not know yet if queries will access only
the last version or not).


Views.
In the case the solution1 is used. Would you used views to do the following
table_t : the table
table_last_version_v : view showing only the last version (with where
last_version_indicator='YES'), used for queries.


In both cases, would you used a view
table_not_transferred_v : view showing records with the
DW_transfer_indicator set to no, that view would be updated by the ETL
processes

or let the the programs do the where DW_transfer_indicator=no.


Thanks

Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 7470 and (514) 925-7187
[EMAIL PROTECTED] 


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Paquette
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Stephane Paquette
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTEC

RE: Design question, historic and views

[Stephane Paquette]

It'a Bill Inmon type ;) (everything is normalized third normal form).

The DW will probably want a mix of last version/all versions.
Reporting or auditing may need to view all versions.

What DW list, I used to be on 2 of them but not enough trafic.


Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 7470 and (514) 925-7187
[EMAIL PROTECTED] 



-Original Message-
DENNIS WILLIAMS
Sent: Tuesday, August 19, 2003 11:55 AM
To: Multiple recipients of list ORACLE-L


Stephane
   What type of DW is this? Is a simple transaction system or is it more
complex? What I'm asking is whether the individual changes are important or
if only the last value is what you want stored. Actually, there is a good,
active data warehousing list that would probably give you better answers.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Tuesday, August 19, 2003 10:34 AM
To: Multiple recipients of list ORACLE-L


Hi,

In an ODS, it is feed in near real time during the day and pushes data to
the DW and other systems at night.
So it may need to keep several copies of the same data (several changes or
the DW can be offline so the the ODS may need to keep several days).

All tables have a surrogated PK and the PK from the source systems are used
as alternate non-unique keys.
All tables will have a DW_transfer indicator (yes/no)
There are 2 designs I can immediately think of :

1. I keep all the data in the same table and I use fields like
last_version_indicator (yes/no), start date, end date.
   This is fast since it implies inserting the new record and updating the
previous record.

2. The last version for each rows are kept in a table and the historic are
kept in a second table.Each table has its historic table. This implies
inserting in the core table, insert the previous record in the historic
table and deleting it.

3. another way ?

The ODS must answer the following:
All changes need to be transferred to the DW not only the last version.
End-users will query the ODS (I do not know yet if queries will access only
the last version or not).


Views.
In the case the solution1 is used. Would you used views to do the following
table_t : the table
table_last_version_v : view showing only the last version (with where
last_version_indicator='YES'), used for queries.


In both cases, would you used a view
table_not_transferred_v : view showing records with the
DW_transfer_indicator set to no, that view would be updated by the ETL
processes

or let the the programs do the where DW_transfer_indicator=no.


Thanks

Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 7470 and (514) 925-7187
[EMAIL PROTECTED] 


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Paquette
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Stephane Paquette
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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, historic and views

[DENNIS WILLIAMS]

Stephane
   What type of DW is this? Is a simple transaction system or is it more
complex? What I'm asking is whether the individual changes are important or
if only the last value is what you want stored. Actually, there is a good,
active data warehousing list that would probably give you better answers.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 
 

-Original Message-
Sent: Tuesday, August 19, 2003 10:34 AM
To: Multiple recipients of list ORACLE-L


Hi,

In an ODS, it is feed in near real time during the day and pushes data to
the DW and other systems at night.
So it may need to keep several copies of the same data (several changes or
the DW can be offline so the the ODS may need to keep several days).

All tables have a surrogated PK and the PK from the source systems are used
as alternate non-unique keys.
All tables will have a DW_transfer indicator (yes/no)
There are 2 designs I can immediately think of :

1. I keep all the data in the same table and I use fields like
last_version_indicator (yes/no), start date, end date.
   This is fast since it implies inserting the new record and updating the
previous record.

2. The last version for each rows are kept in a table and the historic are
kept in a second table.Each table has its historic table. This implies
inserting in the core table, insert the previous record in the historic
table and deleting it.

3. another way ?

The ODS must answer the following:
All changes need to be transferred to the DW not only the last version.
End-users will query the ODS (I do not know yet if queries will access only
the last version or not).


Views.
In the case the solution1 is used. Would you used views to do the following
table_t : the table
table_last_version_v : view showing only the last version (with where
last_version_indicator='YES'), used for queries.


In both cases, would you used a view
table_not_transferred_v : view showing records with the
DW_transfer_indicator set to no, that view would be updated by the ETL
processes

or let the the programs do the where DW_transfer_indicator=no.


Thanks

Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 7470 and (514) 925-7187
[EMAIL PROTECTED] 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Paquette
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.

[Jared . Still]

Well then, that's not an FK.

That's just two columns that the 'duhsigner' is copying to some
other table, presenting a nice little update anomaly.

You can soon expect different programs to return different answers
from the database, depending on which table the data is
queried from.

Jared





"Grabowy, Chris" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
05/31/2002 08:33 AM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Design question.


We have a designer that is adding a FK on two columns from one table to
another.  These two columns are not in the parent table's primary key.

So we are kind of scratching our heads wondering if you can, from a proper
design point of view, create such a FK?  It appears that if you update one
of the two columns in the child table then you would need to create a new
record in the parent table. 

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

Re: Design question.

[Rachel Carmichael]

FK can be on unique columns, not just PK columns and you would not
be allowed to add a row in the child table with a value not in the
parent table unless the child row had a null in it


--- "Grabowy, Chris" <[EMAIL PROTECTED]> wrote:
> We have a designer that is adding a FK on two columns from one table
> to
> another.  These two columns are not in the parent table's primary
> key.
> 
> So we are kind of scratching our heads wondering if you can, from a
> proper
> design point of view, create such a FK?  It appears that if you
> update one
> of the two columns in the child table then you would need to create a
> new
> record in the parent table.  
> 
> Thoughts??
> -- 
> 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).


__
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  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.

[Mercadante, Thomas F]

Title: RE: Design question.



I 
agree that "Not Good" might characterize this - what it points out is "not good" 
table design.  the parent table now will have a row with two unique items - 
the existing primary kay, and a combination of two other 
columns.
 
I 
would review the tabl3e design and decide if something else needs to be 
done.
 
Tom Mercadante Oracle Certified Professional 

  -Original Message-From: Whittle Jerome Contr NCI 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, May 31, 2002 
  12:07 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: Design question.
  Chris, 
  
  Is there a unique 
  constraint based on the two fields in the parent table? If so it might work 
  OK. Still there are the problems of referential integrity, orphan records, 
  etc. If there isn't a unique constraint in the parent table, you can also add 
  in potential many-to-many relationships as a problem to.
  Basically I'd say 
  this falls into the "Not Good" category. 
  Jerry Whittle ACIFICS DBA NCI Information Systems Inc. 
  [EMAIL PROTECTED] 618-622-4145 
  
-Original 
Message- From:   Grabowy, Chris 
[SMTP:[EMAIL PROTECTED]] Sent:   Friday, May 31, 2002 10:33 AM To: 
Multiple recipients of list ORACLE-L 
Subject:    Design question. 
We have a designer that is 
adding a FK on two columns from one table to another.  These two columns are not 
in the parent table's primary key. 
So we are kind of scratching our 
heads wondering if you can, from a proper design point of view, create such a 
FK?  It appears that if you update one of the two columns in the child table 
then you would need to create a new record in the parent table.  
Thoughts?? 

RE: Design question.

[Whittle Jerome Contr NCI]

Title: RE: Design question.






Chris,


Is there a unique constraint based on the two fields in the parent table? If so it might work OK. Still there are the problems of referential integrity, orphan records, etc. If there isn't a unique constraint in the parent table, you can also add in potential many-to-many relationships as a problem to.

Basically I'd say this falls into the "Not Good" category.


Jerry Whittle

ACIFICS DBA

NCI Information Systems Inc.

[EMAIL PROTECTED]

618-622-4145


-Original Message-

From:   Grabowy, Chris [SMTP:[EMAIL PROTECTED]]

Sent:   Friday, May 31, 2002 10:33 AM

To: Multiple recipients of list ORACLE-L

Subject:    Design question.


We have a designer that is adding a FK on two columns from one table to

another.  These two columns are not in the parent table's primary key.


So we are kind of scratching our heads wondering if you can, from a proper

design point of view, create such a FK?  It appears that if you update one

of the two columns in the child table then you would need to create a new

record in the parent table.  


Thoughts??

RE: Design question.

[Mercadante, Thomas F]

Chris,

The column in the parent table needs to be the PK or be unique.
Should be no problem.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Friday, May 31, 2002 11:33 AM
To: Multiple recipients of list ORACLE-L


We have a designer that is adding a FK on two columns from one table to
another.  These two columns are not in the parent table's primary key.

So we are kind of scratching our heads wondering if you can, from a proper
design point of view, create such a FK?  It appears that if you update one
of the two columns in the child table then you would need to create a new
record in the parent table.  

Thoughts??
-- 
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: Mercadante, Thomas F
  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...

[Stephane Faroult]

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 l

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

[Abdul Aleem]

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

[Igor Neyman]

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 rem

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

[Koivu, Lisa]

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

[Jared . Still]

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

Re: Design question...

[Jared . Still]

Chris,

I'm rather anal about using generated keys, and *I* wouldn't
even use them here.  ;)

Lookup tables with information that is likely to remain static 
and have an obvious key should be fine without a generated
key.  ( States are a good candidate )

Jared





"Grabowy, Chris" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
04/17/02 10:18 AM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Design question...


To simplify my question, if I am creating a STATE table to hold all the
states of the US, should I create it like this...

Name  Null?Type
- 

STATE_CODENOT NULL CHAR(2) <-- PK

STATE_DESCNOT NULL VARCHAR2(50)

or like this...

Name  Null?Type
- 

STATE_ID  NOT NULL NUMBER  <-- PK 
STATE_CODENOT NULL CHAR(2)
STATE_DESCNOT NULL VARCHAR2(50)

I'm trying to figure out which is more efficient, STATE_CODE or STATE_ID,
when doing a PK lookup, dealing with FKs, etc. 

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

RE: Design question...

[Grabowy, Chris]

We have a country_code table to represent countries, but the first version
of the app is supposed to be the U.S. only version.  Obviously, any
flexibility that we can design in right now can only help in the next phase.

It's the client's left hand that keeps saying this will only be used in the
US, but the right hand keeps saying that a future version of the app will be
international...but were not allowed to ask about international requirements
or specs.  Sigh.

-Original Message-
Sent: Wednesday, April 17, 2002 3:04 PM
To: Multiple recipients of list ORACLE-L



One thing to consider is country codes.  I have seen many applications
where two character country codes have been crammed into the STATE_CDS
table.  Usually the two character state abbreviation is the primary key,
but that means that some smaller countries end up with "abbreviations" that
have no relationship to the county's name.  If you're absolutely certain
that your application will be limited to the US, then using hte
abbreviations makes a certain sense.  If there's the possibility that the
application will use data from outside the US then it's probably best to
use some sequence generated primary key.

Just my $.02 (US)



 

Bjørn Engsig



>cc:

        Sent by: rootSubject: Re: Design question...

 

 

04/17/2002

02:21 PM

Please

respond to

ORACLE-L

 

 





There is hardly any difference, except the waste of space for the state_id.
But purists will tell you, that you should not put business information
into
primary key columns, and therefore state_code should not be a primary key.
And if a state is gets a new code, it is much easier to change your data if
state_code is not PK/FK

Rgds, Bjørn.

On Wednesday 17 April 2002 19:18, Grabowy, Chris wrote:
> To simplify my question, if I am creating a STATE table to hold all the
> states of the US, should I create it like this...
>
> Name  Null?Type
> - 
> 
> STATE_CODENOT NULL CHAR(2) <-- PK
>
> STATE_DESCNOT NULL VARCHAR2(50)
>
> or like this...
>
> Name  Null?Type
> - 
> 
> STATE_ID  NOT NULL NUMBER  <-- PK
> STATE_CODENOT NULL CHAR(2)
> STATE_DESCNOT NULL VARCHAR2(50)
>
> I'm trying to figure out which is more efficient, STATE_CODE or STATE_ID,
> when doing a PK lookup, dealing with FKs, etc.
>
> Many TIA!!!
>
> Chris

--
Bjørn Engsig, Miracle A/S
http://MiracleAS.dk
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Bj=F8rn=20Engsig?=
  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).
--
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).

Re: Design question...

[tday6]

One thing to consider is country codes.  I have seen many applications
where two character country codes have been crammed into the STATE_CDS
table.  Usually the two character state abbreviation is the primary key,
but that means that some smaller countries end up with "abbreviations" that
have no relationship to the county's name.  If you're absolutely certain
that your application will be limited to the US, then using hte
abbreviations makes a certain sense.  If there's the possibility that the
application will use data from outside the US then it's probably best to
use some sequence generated primary key.

Just my $.02 (US)



   

Bjørn Engsig   



>cc:   

Sent by: root    Subject: Re: Design question...   

   

   

04/17/2002 

02:21 PM   

Please 

respond to 

ORACLE-L   

   

   





There is hardly any difference, except the waste of space for the state_id.
But purists will tell you, that you should not put business information
into
primary key columns, and therefore state_code should not be a primary key.
And if a state is gets a new code, it is much easier to change your data if
state_code is not PK/FK

Rgds, Bjørn.

On Wednesday 17 April 2002 19:18, Grabowy, Chris wrote:
> To simplify my question, if I am creating a STATE table to hold all the
> states of the US, should I create it like this...
>
> Name  Null?Type
> - 
> 
> STATE_CODENOT NULL CHAR(2) <-- PK
>
> STATE_DESCNOT NULL VARCHAR2(50)
>
> or like this...
>
> Name  Null?Type
> - 
> 
> STATE_ID  NOT NULL NUMBER  <-- PK
> STATE_CODENOT NULL CHAR(2)
> STATE_DESCNOT NULL VARCHAR2(50)
>
> I'm trying to figure out which is more efficient, STATE_CODE or STATE_ID,
> when doing a PK lookup, dealing with FKs, etc.
>
> Many TIA!!!
>
> Chris

--
Bjørn Engsig, Miracle A/S
http://MiracleAS.dk
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Bj=F8rn=20Engsig?=
  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).

RE: Design question...

[Grabowy, Chris]

Several people have replied back to me about the design rule to not use
business data in the primary key, which I agree with.  Although I don't plan
on creating a permanent STATE_SEQ sequence, since it will only be used once
to populate the table.  

Any other thoughts or arguments??  TIA!!

-Original Message-
Sent: Wednesday, April 17, 2002 1:19 PM
To: Multiple recipients of list ORACLE-L


To simplify my question, if I am creating a STATE table to hold all the
states of the US, should I create it like this...

Name  Null?Type
- 

STATE_CODENOT NULL CHAR(2) <-- PK

STATE_DESCNOT NULL VARCHAR2(50)

or like this...

Name  Null?Type
- 

STATE_ID  NOT NULL NUMBER  <-- PK 
STATE_CODENOT NULL CHAR(2)
STATE_DESCNOT NULL VARCHAR2(50)

I'm trying to figure out which is more efficient, STATE_CODE or STATE_ID,
when doing a PK lookup, dealing with FKs, etc.  

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

RE: Design question...

[Koivu, Lisa]

Well Chris, as you are probably aware, there is a theoretical train of
thought that says that every table should have a primary key that is an
arbitrary ID, not related to the data in any way shape or form.  The benefit
is that data that is SUPPOSSED to be unique (like ssn's) can break and your
application can most likely still run.  I have taken the road of ID as
primary key, unique key on what would be a natural primary key in my
designs. 

However I doubt we'll have two MN's, two FL's, two AR's in the near future.


Efficiency?  I would think the number would be more efficient but I have no
concrete evidence to back this up. 

Two cents from an extremely hormonal furnace...

Lisa Koivu
Oracle Database Tank
Fairfield Resorts, Inc.
954-935-4117


> -Original Message-
> From: Grabowy, Chris [SMTP:[EMAIL PROTECTED]]
> Sent: Wednesday, April 17, 2002 1:19 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:  Design question...
> 
> To simplify my question, if I am creating a STATE table to hold all the
> states of the US, should I create it like this...
> 
> Name  Null?Type
> - 
> 
> STATE_CODENOT NULL CHAR(2) <-- PK
> 
> STATE_DESCNOT NULL VARCHAR2(50)
> 
> or like this...
> 
> Name  Null?Type
> - 
> 
> STATE_ID  NOT NULL NUMBER  <-- PK 
> STATE_CODENOT NULL CHAR(2)
> STATE_DESCNOT NULL VARCHAR2(50)
> 
> I'm trying to figure out which is more efficient, STATE_CODE or STATE_ID,
> when doing a PK lookup, dealing with FKs, etc.  
> 
> Many 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...

[Brian Haas]

On Wed, 2002-04-17 at 10:18, Grabowy, Chris wrote:
> To simplify my question, if I am creating a STATE table to hold all the
> states of the US, should I create it like this...

Chris,
  We use a state_id field which is a number.Usually you want to use
numbers or integers as primary keys, but since my guess is you'll have
about 50 rows ;-) I don't think the performance will be to bad if you
want to keep the code as a char. Of course that also depends on how hard
you think the table will be hit

HTH,

-Brian

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Brian Haas
  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...

[Mercadante, Thomas F]

Chris,

I would prefer the first method.  As far as I am concerned, the only reason
to use a sequence number as a primary key is if the value for the natural
primary could could possibly change.

In this case, the two char state codes have been around for a very long time
and would probably not change.

The other think I would be concerned with is considering Canadian Province
codes.  Would they fit within a "STATES" table structure?  

This crazy world of ours is getting soo small, that thinking
provincially about state codes may no longer be correct anymore.

hope this helps.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Wednesday, April 17, 2002 1:19 PM
To: Multiple recipients of list ORACLE-L


To simplify my question, if I am creating a STATE table to hold all the
states of the US, should I create it like this...

Name  Null?Type
- 

STATE_CODENOT NULL CHAR(2) <-- PK

STATE_DESCNOT NULL VARCHAR2(50)

or like this...

Name  Null?Type
- 

STATE_ID  NOT NULL NUMBER  <-- PK 
STATE_CODENOT NULL CHAR(2)
STATE_DESCNOT NULL VARCHAR2(50)

I'm trying to figure out which is more efficient, STATE_CODE or STATE_ID,
when doing a PK lookup, dealing with FKs, etc.  

Many 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: Mercadante, Thomas F
  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...

[Pardee, Roy E]

If you go with the first option, you will likely be able to get out of
joining your STATE table to the referencing tables in a bunch of cases
(since the 2-letter abbreviation is interpretable on its own).  But if
you'll wind up having to do the join anyway (e.g., to display the
STATE_DESC) then those joins will likely be faster on a numeric...

HTH,

-Roy

Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487

-Original Message-
Sent: Wednesday, April 17, 2002 10:19 AM
To: Multiple recipients of list ORACLE-L


To simplify my question, if I am creating a STATE table to hold all the
states of the US, should I create it like this...

Name  Null?Type
- 

STATE_CODENOT NULL CHAR(2) <-- PK

STATE_DESCNOT NULL VARCHAR2(50)

or like this...

Name  Null?Type
- 

STATE_ID  NOT NULL NUMBER  <-- PK 
STATE_CODENOT NULL CHAR(2)
STATE_DESCNOT NULL VARCHAR2(50)

I'm trying to figure out which is more efficient, STATE_CODE or STATE_ID,
when doing a PK lookup, dealing with FKs, etc.  

Many 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: Pardee, Roy E
  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...

[Toepke, Kevin M]

H

from a pure relational standpoint (something like 4th or 5th normal form),
keys shouldn't have any inherent meaning so you should go with STATE_ID as
the PK.

>From a real-world perspective, I would use STATE_CD as the PK. Its not going
to change very often (how often do state abbreviations change?) unless you
are going international. And, if you have the appropriate indexes on the fk
columns you can eliminate a join from many queries.

>From a space perspective, there is no difference:
SELECT VSIZE(1) FROM dual;  -- result is 2
SELECT VSIZE(50) FROM dual; -- result is 2
SELECT VSIZE('AK') FROM dual;   -- result is 2
I've never seen a noticable difference in performance or FK management
either.

Caver

-Original Message-
Sent: Wednesday, April 17, 2002 1:19 PM
To: Multiple recipients of list ORACLE-L


To simplify my question, if I am creating a STATE table to hold all the
states of the US, should I create it like this...

Name  Null?Type
- 

STATE_CODENOT NULL CHAR(2) <-- PK

STATE_DESCNOT NULL VARCHAR2(50)

or like this...

Name  Null?Type
- 

STATE_ID  NOT NULL NUMBER  <-- PK 
STATE_CODENOT NULL CHAR(2)
STATE_DESCNOT NULL VARCHAR2(50)

I'm trying to figure out which is more efficient, STATE_CODE or STATE_ID,
when doing a PK lookup, dealing with FKs, etc.  

Many 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: Toepke, Kevin M
  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...

[Bjørn Engsig]

There is hardly any difference, except the waste of space for the state_id.  
But purists will tell you, that you should not put business information into 
primary key columns, and therefore state_code should not be a primary key.  
And if a state is gets a new code, it is much easier to change your data if 
state_code is not PK/FK

Rgds, Bjørn.

On Wednesday 17 April 2002 19:18, Grabowy, Chris wrote:
> To simplify my question, if I am creating a STATE table to hold all the
> states of the US, should I create it like this...
>
> Name  Null?Type
> - 
> 
> STATE_CODENOT NULL CHAR(2) <-- PK
>
> STATE_DESCNOT NULL VARCHAR2(50)
>
> or like this...
>
> Name  Null?Type
> - 
> 
> STATE_ID  NOT NULL NUMBER  <-- PK
> STATE_CODENOT NULL CHAR(2)
> STATE_DESCNOT NULL VARCHAR2(50)
>
> I'm trying to figure out which is more efficient, STATE_CODE or STATE_ID,
> when doing a PK lookup, dealing with FKs, etc.
>
> Many TIA!!!
>
> Chris

-- 
Bjørn Engsig, Miracle A/S
http://MiracleAS.dk
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Bj=F8rn=20Engsig?=
  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: 1 or many tables and other thoughts

[Stephane Faroult]

Tracy Rahmlow wrote:
> 
> My question deals with what is the best practice in creating lookup tables.
> Many of these tables contain very limited information such as "code &
> description".  For example:
> 
> Agency
> 1 - Jones Inc
> 2 - Ratfield Co
> 
> Source
> 1 - phone
> 2 - mail
> 3 - internet
> 
> Is it better to create 1 composite table to contain this data or have separate
> tables for each?  If a composite table is the way to go, how far do you take
> it?  Ie, what if the entity has more than just 2 columns worth of data.  What
> is the best way to enforce that valid values are actually stored in the table,
> since foreign keys can't be used to these table types?  (For example, if a
> sales table has a source column, how should you enforce that valid sources are
> actually stored in the row?)
>

I like your example, because there are two very different things here:
something which can possibly be dynamic (Agency) and something which
stands very good chance of remaining static (source). IMHO, the first
one should go to a lookup table, with FK and all, while for the second
one meaning should be hardcoded with a DECODE when you need it, and
integrity enforced by a CHECK constraint, i.e source_code in (1, 2, 3).
Updating the CHECK constraint when you have a new source to add is not
so much of a pain in terms of maintenance.
To answer your 'one composite table' question, I am against, because it
would prevent you from using the basic FK mechanisms and you would have
to write triggers-of-death to ensure integrity. Would be ugly. That
said, try to use CHECK rather than a FK when you have a reasonably weak
(say, under 25) number of values to check and that you are not likely to
change the values every week. I have carried out a number of tests, if
the cost of an insert in a table is 100, adding a foreign key make it
jump to 60 (and adding one index more than doubles the cost). In
comparison, CHECK costs next to nothing.

-- 
Regards,

Stephane Faroult
Oriole Ltd
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  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 DW

[Aponte, Tony]

Title: RE: Design question DW






I consider the skillset I acquired as an extension of the OLTP ones I already had.  Some lessons I learned: 

think big (lots of data, parallelize queries and object creation/maintenance), 

move to SAFE tablespace layouts (famous paper 711) to reduce object sizing and defrag work,

bone up on partitioning features and caveats,

ditto for locally-managed tablespaces,

denormalize tables to speed query response times,

don't denormalize to make it easier for developing SQL,

live without database-enforced constraints that slow down DML (enforce it on the load programs)

don't run in archivelog mode,

replace traditional cold backups with transportable tablespaces (will keep your DW in read-only mode while backup executes)

wide-striping performed better that manual object/datafile/device I/O balancing,

and I can't emphasize this enough, get design help from the professionals.  Our 1st through 4th attempt at deploying a DW were driven by OLTP programmers.  In the words of an independent audit (read $$$) conducted on our DW "It's an OLTP programmer's view of a DW.  Keep the named of the DW and trash the rest."  Heads rolled, money was misspent and I lost some more hair.

If you haven't already done so I suggest reading almost everything in http://www.billinmon.com/  http://www.ralphkimball.com/ to get up to speed on DW concepts.  I'm not totally sure but I think your case is referred to a multi-values source system fields and has been addressed in one of the two sites.  I learned that for starters the approach I would take is to gather typical questions that are asked of the OLTP system (i.e.. foreign cars that used p240 tires, foreign cars that take 2 or more tire sizes, all cars that use p250 and p200).  Each one of these implies that the multi-values column should be parsed out into separate tables (one dimension table for cars, one for tire sizes, and another helper table for car/tire size rows).  You'll find many of these terms in the two websites.  The 3 tables could answer the examples as follows (excuse my syntax errors):

Foreign cars that used p240 tires:


Select 

  car,count(*)

from 

  car_table, car_tire_table

where 

  car_table.domestic=false

and   

  car_table.car = car_tire_table.car

and   

  car_tire_table.tire_size = 'p240';


Foreign cars that take 2 or more tire sizes:


Select 

  car,count(*)

from 

  car_table, car_tire_table

where 

  car_table.domestic=false

and   

  car_table.car = car_tire_table.car

group by 

  car

having 

  count(*) > 1;



All cars that use p250 and p200 tires


Select

  car

from

  car_tire_table

where

  car_tire_table.tire_size in ('p250','p200');



My point here is to show that gathering few common questions coupled with a little knowledge of DW concepts can jumpstart your thinking in DW design terms (or scare you enough to seek out people that do it for a living).

HTH

Tony Aponte



-Original Message-

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]

Sent: Friday, October 19, 2001 10:35 AM

To: Multiple recipients of list ORACLE-L

Subject: Design question DW



Hello List,


 I have a design question for a datawarehouse

application.  I have done OLTP design, but no DW

design before.  The issue is that this warehouse is

being fed from an existing system that allows

multi-values.  What I mean by multi-values is a

column that has comma delimited values in it.  So for

example, if there is a car table with a column that

says tire_size, if the car can handle tire sizes of

p200, p240, and p250 then for a select on the one row

with that car in it, under the tire column you would

have p200,p240,p250.  

I would never even think about letting this thru in

an OLTP system, and if I didn't have access to the

list I wouldn't allow it in a data warehouse either. 

But, since I have the advantage of your expertise, I

thought I might as well use it.  There are about 40

of these columns in 5 tables in the proposed design

by the developer.  The developer is trying to say

that they won't use these columns in the where

clauses on most of them and that they are just

detailed data for display.  I have been around long

enough to know how fast that changes, and all of a

sudden the database is a dog and everyone is blaming

the DBA.  

 So my question is, am I just an OLTP DBA in new

territory, or the same principles apply, and

multi-values are still one of the biggest relational

design disasters regardless of OLTP or DW.


Any help is appreciated,

Mike




You too can have your own email address from Eurosport.

http://www.eurosport.com






-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: 

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538

RE: Design Question - Thoughts Needed

[Mercadante, Thomas F]

Tracy, 

>The 1000 comp deductible does not equate to the same premium for all
vehicles.
>We also have one coverage where 3 columns are necessary to capture the covg
>info.  There is a deductible, limit and premium associated with it.  So do
we
>stick another column in the coverage table to capture for only this
coverage?

The short answer to your question is, yes, you design the vehicle-coverage
table to meet the requirements of your application.  Remember, each database
table is an entity unto itself, and should be able to stand alone with the
information that it is supposed to hold.  In your case, if you decide that
the vehicle-coverage table will hold the premium for each type of coverage
for each vehicle, you place whatever columns you need to support the capture
of the premium (as long as you do not violate normalization).  It seems to
me that you would have one column in the table that would hold the premium
amount for all coverage types (for ease of reporting).  If you need
additional columns to explain how you calculated that amount, just add'em.


Yes, design is an art, not a science.  After awhile, you will develop your
own standards for database design, and it will get easier.

hope this helps.

Tom Mercadante
Oracle Certified Professional

-Original Message-
Sent: Monday, June 25, 2001 5:23 PM
To: Multiple recipients of list ORACLE-L


I think I may have confused some, regarding my previous post.  The coverage
and
amount is specific to the vehicle.  It is not a set amount.  For example:

Method #1
Vehicle/Coverage Table
Seq  Year Type Annual_Miles   Comp_Ded   Comp_Prem  Towing_Ded
Towing_PremPip_Ded   Pip_Limit Pip_Prem
197   Jeep 1000  500   25.25   5010
"NULL"
 "NULL" "NULL"
299   Truck 7000  1000   20.50  "NULL"
"NULL"
 "NULL" "NULL" "NULL"
3  98   Car  5000  1000   15.00  0 30
250   10 35

Method #2
Vehicle Table
Seq  Year Type Annual_Miles
197   Jeep 1000
299   Truck 7000
398   Car  5000

Coverage Table
Seq  Covg_Id   Covg_Ded  Covg_Limit  Covg_Prem
1A(Comp)   500   "NULL"  25.25
1B(Towing) 50"NULL"  10
2A 1000  "NULL"  20.50
3A 1000  "NULL"  15
3B 0 "NULL"  30
3C(Pip) 250   10  35

The 1000 comp deductible does not equate to the same premium for all
vehicles.
We also have one coverage where 3 columns are necessary to capture the covg
info.  There is a deductible, limit and premium associated with it.  So do
we
stick another column in the coverage table to capture for only this
coverage?
The saga continues.  Now I understand when they say modeling is an art and
not
a science.  I was much better in science.  (Thus my struggles)

-- Forwarded by Tracy Rahmlow on 06/25/2001 01:52 PM
Please respond to [EMAIL PROTECTED]
Sent by:  [EMAIL PROTECTED]
To:   "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
cc:

Tracy,

I would fight tooth and nail for a separate auto_coverage table that is a
child to each parent auto record. (It actually represents a many-to-many
relationship between the auto table and the available_coverage table which
lists all coverages available).

When you argue about performance problems during reporting, it pales in
comparison to performance problems if, say, the coverage name changes, or if
you want to know "Who/how many people have just towing".  In my example, you
query the child table for the coverage code that = towing.  In your example,
you query all parent records.

This is the real reason why normalization is required in relational design.
Break everything down into it's smallest parts so you can query and update
just the atomic item you need.

just my 2 cents.

Tom Mercadante
Oracle Certified Professional

-Original Message-
Sent: Monday, June 25, 2001 1:16 PM
To: Multiple recipients of list ORACLE-L


We are looking to redesign our current system and a question popped up that
I
am struggling with.  (our business is auto insurance).  Each vehicle on a
policy may have 1 or many coverages.For example, comprehensive,
collision,
towing, property damage ...  A couple of things: 1-  some coverages will not
apply because they are state specific.   2 - some coverages will not be
chosen
by the insured.  Therefore, should I have a vehicle table with each coverage
and its premium as separate specific columns or should I add a coverage
table
which will contain the coverage and premium.  Under the later example if I
have
10 coverages and I want to print them on the insured's declaration page
don't I
have a possible performance problem with up to 10 i/os.  Where as with the
first example I only have 1 read but possible space wastage.   I know in 1st
normal form you should remove repeating groups, in my case is a coverage a
repeati

RE: Design Question - Thoughts Needed

[Tracy Rahmlow]

Please see prior post.  The formatting seems to be hosed up.



-- Forwarded by Tracy Rahmlow on 06/25/2001 03:15 PM
---


Please respond to [EMAIL PROTECTED]

Sent by:  [EMAIL PROTECTED]


To:   "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
cc:


I think I may have confused some, regarding my previous post.  The coverage and
amount is specific to the vehicle.  It is not a set amount.  For example:

Method #1
Vehicle/Coverage Table
Seq  Year Type Annual_Miles   Comp_Ded   Comp_Prem  Towing_Ded
Towing_PremPip_Ded   Pip_Limit Pip_Prem
197   Jeep 1000  500   25.25   5010"NULL"
 "NULL" "NULL"
299   Truck 7000  1000   20.50  "NULL" "NULL"
 "NULL" "NULL" "NULL"
3  98   Car  5000  1000   15.00  0 30
250   10 35

Method #2
Vehicle Table
Seq  Year Type Annual_Miles
197   Jeep 1000
299   Truck 7000
398   Car  5000

Coverage Table
Seq  Covg_Id   Covg_Ded  Covg_Limit  Covg_Prem
1A(Comp)   500   "NULL"  25.25
1B(Towing) 50"NULL"  10
2A 1000  "NULL"  20.50
3A 1000  "NULL"  15
3B 0 "NULL"  30
3C(Pip) 250   10  35


The 1000 comp deductible does not equate to the same premium for all vehicles.
We also have one coverage where 3 columns are necessary to capture the covg
info.  There is a deductible, limit and premium associated with it.  So do we
stick another column in the coverage table to capture for only this coverage?
The saga continues.  Now I understand when they say modeling is an art and not
a science.  I was much better in science.  (Thus my struggles)

-- Forwarded by Tracy Rahmlow on 06/25/2001 01:52 PM
---

  06/25/2001 10:18 AM PST

Please respond to [EMAIL PROTECTED]

Sent by:  [EMAIL PROTECTED]


To:   "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
cc:


Tracy,

I would fight tooth and nail for a separate auto_coverage table that is a
child to each parent auto record. (It actually represents a many-to-many
relationship between the auto table and the available_coverage table which
lists all coverages available).

When you argue about performance problems during reporting, it pales in
comparison to performance problems if, say, the coverage name changes, or if
you want to know "Who/how many people have just towing".  In my example, you
query the child table for the coverage code that = towing.  In your example,
you query all parent records.

This is the real reason why normalization is required in relational design.
Break everything down into it's smallest parts so you can query and update
just the atomic item you need.

just my 2 cents.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Monday, June 25, 2001 1:16 PM
To: Multiple recipients of list ORACLE-L


We are looking to redesign our current system and a question popped up that
I
am struggling with.  (our business is auto insurance).  Each vehicle on a
policy may have 1 or many coverages.For example, comprehensive,
collision,
towing, property damage ...  A couple of things: 1-  some coverages will not
apply because they are state specific.   2 - some coverages will not be
chosen
by the insured.  Therefore, should I have a vehicle table with each coverage
and its premium as separate specific columns or should I add a coverage
table
which will contain the coverage and premium.  Under the later example if I
have
10 coverages and I want to print them on the insured's declaration page
don't I
have a possible performance problem with up to 10 i/os.  Where as with the
first example I only have 1 read but possible space wastage.   I know in 1st
normal form you should remove repeating groups, in my case is a coverage a
repeating group?   Where do you draw the line?  For example, clients may
have
multiple phone numbers although I don't see many examples where the phone
numbers are split into another table.  Please share any thoughts that may
make
my decision easier.  Thanks

Just an fyi, in our current design the coverages have been stored with the
vehicle.


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tracy Rahmlow
  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 - Thoughts Needed

[Tracy Rahmlow]

I think I may have confused some, regarding my previous post.  The coverage and
amount is specific to the vehicle.  It is not a set amount.  For example:

Method #1
Vehicle/Coverage Table
Seq  Year Type Annual_Miles   Comp_Ded   Comp_Prem  Towing_Ded
Towing_PremPip_Ded   Pip_Limit Pip_Prem
197   Jeep 1000  500   25.25   5010"NULL"
 "NULL" "NULL"
299   Truck 7000  1000   20.50  "NULL" "NULL"
 "NULL" "NULL" "NULL"
3  98   Car  5000  1000   15.00  0 30
250   10 35

Method #2
Vehicle Table
Seq  Year Type Annual_Miles
197   Jeep 1000
299   Truck 7000
398   Car  5000

Coverage Table
Seq  Covg_Id   Covg_Ded  Covg_Limit  Covg_Prem
1A(Comp)   500   "NULL"  25.25
1B(Towing) 50"NULL"  10
2A 1000  "NULL"  20.50
3A 1000  "NULL"  15
3B 0 "NULL"  30
3C(Pip) 250   10  35


The 1000 comp deductible does not equate to the same premium for all vehicles.
We also have one coverage where 3 columns are necessary to capture the covg
info.  There is a deductible, limit and premium associated with it.  So do we
stick another column in the coverage table to capture for only this coverage?
The saga continues.  Now I understand when they say modeling is an art and not
a science.  I was much better in science.  (Thus my struggles)

-- Forwarded by Tracy Rahmlow on 06/25/2001 01:52 PM
---

  06/25/2001 10:18 AM PST

Please respond to [EMAIL PROTECTED]

Sent by:  [EMAIL PROTECTED]


To:   "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
cc:


Tracy,

I would fight tooth and nail for a separate auto_coverage table that is a
child to each parent auto record. (It actually represents a many-to-many
relationship between the auto table and the available_coverage table which
lists all coverages available).

When you argue about performance problems during reporting, it pales in
comparison to performance problems if, say, the coverage name changes, or if
you want to know "Who/how many people have just towing".  In my example, you
query the child table for the coverage code that = towing.  In your example,
you query all parent records.

This is the real reason why normalization is required in relational design.
Break everything down into it's smallest parts so you can query and update
just the atomic item you need.

just my 2 cents.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Monday, June 25, 2001 1:16 PM
To: Multiple recipients of list ORACLE-L


We are looking to redesign our current system and a question popped up that
I
am struggling with.  (our business is auto insurance).  Each vehicle on a
policy may have 1 or many coverages.For example, comprehensive,
collision,
towing, property damage ...  A couple of things: 1-  some coverages will not
apply because they are state specific.   2 - some coverages will not be
chosen
by the insured.  Therefore, should I have a vehicle table with each coverage
and its premium as separate specific columns or should I add a coverage
table
which will contain the coverage and premium.  Under the later example if I
have
10 coverages and I want to print them on the insured's declaration page
don't I
have a possible performance problem with up to 10 i/os.  Where as with the
first example I only have 1 read but possible space wastage.   I know in 1st
normal form you should remove repeating groups, in my case is a coverage a
repeating group?   Where do you draw the line?  For example, clients may
have
multiple phone numbers although I don't see many examples where the phone
numbers are split into another table.  Please share any thoughts that may
make
my decision easier.  Thanks

Just an fyi, in our current design the coverages have been stored with the
vehicle.


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tracy Rahmlow
  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: Mercadante, Thomas F
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
---

RE: Design Question - Thoughts Needed

[Christopher Spence]

I would have a three table set up.

Your policy detail table, your vehicle table, and your vehicle_policy lookup
table.
I generally use this format to resolve many to many.  Most of the time you
either know the vehicle or policy type.  Which will keep you in the lookup
table and only go to the appropriate records in the parent table.

"Walking on water and developing software from a specification are easy if
both are frozen."

Christopher R. Spence
Oracle DBA
Fuelspot 



-Original Message-
Sent: Monday, June 25, 2001 1:16 PM
To: Multiple recipients of list ORACLE-L


We are looking to redesign our current system and a question popped up that
I
am struggling with.  (our business is auto insurance).  Each vehicle on a
policy may have 1 or many coverages.For example, comprehensive,
collision,
towing, property damage ...  A couple of things: 1-  some coverages will not
apply because they are state specific.   2 - some coverages will not be
chosen
by the insured.  Therefore, should I have a vehicle table with each coverage
and its premium as separate specific columns or should I add a coverage
table
which will contain the coverage and premium.  Under the later example if I
have
10 coverages and I want to print them on the insured's declaration page
don't I
have a possible performance problem with up to 10 i/os.  Where as with the
first example I only have 1 read but possible space wastage.   I know in 1st
normal form you should remove repeating groups, in my case is a coverage a
repeating group?   Where do you draw the line?  For example, clients may
have
multiple phone numbers although I don't see many examples where the phone
numbers are split into another table.  Please share any thoughts that may
make
my decision easier.  Thanks

Just an fyi, in our current design the coverages have been stored with the
vehicle.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tracy Rahmlow
  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: Christopher Spence
  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 - Thoughts Needed

[Big Planet]

I would go for creating a separate table for coverage , to make design more
flexible. If you add 10 cols in vehicle table and and a new coverage is
added later you will have to alter your table.
Still you think performance and space you can cluster the two tables .
-Big P


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, June 25, 2001 10:15 AM


> We are looking to redesign our current system and a question popped up
that I
> am struggling with.  (our business is auto insurance).  Each vehicle on a
> policy may have 1 or many coverages.For example, comprehensive,
collision,
> towing, property damage ...  A couple of things: 1-  some coverages will
not
> apply because they are state specific.   2 - some coverages will not be
chosen
> by the insured.  Therefore, should I have a vehicle table with each
coverage
> and its premium as separate specific columns or should I add a coverage
table
> which will contain the coverage and premium.  Under the later example if I
have
> 10 coverages and I want to print them on the insured's declaration page
don't I
> have a possible performance problem with up to 10 i/os.  Where as with the
> first example I only have 1 read but possible space wastage.   I know in
1st
> normal form you should remove repeating groups, in my case is a coverage a
> repeating group?   Where do you draw the line?  For example, clients may
have
> multiple phone numbers although I don't see many examples where the phone
> numbers are split into another table.  Please share any thoughts that may
make
> my decision easier.  Thanks
>
> Just an fyi, in our current design the coverages have been stored with the
> vehicle.
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Tracy Rahmlow
>   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: Big Planet
  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 - Thoughts Needed

[Toepke, Kevin M]

Tracy:

I'd make the coverages their own entity. And use a cross-reference table to
determine if a specific coverage is available in given state. This stuff
needs to be entity driven because it is so dynamic (who knows, your company
may stop offering coverage in, say New Jersey.)

As for I/Os, I wouldn't worry about them too much. These tables are probably
small enought that you could cache them (ALTER TABLE ... CACHE) to virtually
eliminate physical I/Os. 

As for when you draw the line, if you have 1 or 2 and the number is not
likely to change in the near future, then store them in the parent entity
(e.g., phone numbers). If the number gets much higher or is a highly
variable, then store in a separate entity.

Kevin T

-Original Message-
Sent: Monday, June 25, 2001 1:16 PM
To: Multiple recipients of list ORACLE-L


We are looking to redesign our current system and a question popped up that
I
am struggling with.  (our business is auto insurance).  Each vehicle on a
policy may have 1 or many coverages.For example, comprehensive,
collision,
towing, property damage ...  A couple of things: 1-  some coverages will not
apply because they are state specific.   2 - some coverages will not be
chosen
by the insured.  Therefore, should I have a vehicle table with each coverage
and its premium as separate specific columns or should I add a coverage
table
which will contain the coverage and premium.  Under the later example if I
have
10 coverages and I want to print them on the insured's declaration page
don't I
have a possible performance problem with up to 10 i/os.  Where as with the
first example I only have 1 read but possible space wastage.   I know in 1st
normal form you should remove repeating groups, in my case is a coverage a
repeating group?   Where do you draw the line?  For example, clients may
have
multiple phone numbers although I don't see many examples where the phone
numbers are split into another table.  Please share any thoughts that may
make
my decision easier.  Thanks

Just an fyi, in our current design the coverages have been stored with the
vehicle.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tracy Rahmlow
  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: Toepke, Kevin M
  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 - Thoughts Needed

[Mercadante, Thomas F]

Tracy,

I would fight tooth and nail for a separate auto_coverage table that is a
child to each parent auto record. (It actually represents a many-to-many
relationship between the auto table and the available_coverage table which
lists all coverages available).

When you argue about performance problems during reporting, it pales in
comparison to performance problems if, say, the coverage name changes, or if
you want to know "Who/how many people have just towing".  In my example, you
query the child table for the coverage code that = towing.  In your example,
you query all parent records.

This is the real reason why normalization is required in relational design.
Break everything down into it's smallest parts so you can query and update
just the atomic item you need.

just my 2 cents.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Monday, June 25, 2001 1:16 PM
To: Multiple recipients of list ORACLE-L


We are looking to redesign our current system and a question popped up that
I
am struggling with.  (our business is auto insurance).  Each vehicle on a
policy may have 1 or many coverages.For example, comprehensive,
collision,
towing, property damage ...  A couple of things: 1-  some coverages will not
apply because they are state specific.   2 - some coverages will not be
chosen
by the insured.  Therefore, should I have a vehicle table with each coverage
and its premium as separate specific columns or should I add a coverage
table
which will contain the coverage and premium.  Under the later example if I
have
10 coverages and I want to print them on the insured's declaration page
don't I
have a possible performance problem with up to 10 i/os.  Where as with the
first example I only have 1 read but possible space wastage.   I know in 1st
normal form you should remove repeating groups, in my case is a coverage a
repeating group?   Where do you draw the line?  For example, clients may
have
multiple phone numbers although I don't see many examples where the phone
numbers are split into another table.  Please share any thoughts that may
make
my decision easier.  Thanks

Just an fyi, in our current design the coverages have been stored with the
vehicle.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tracy Rahmlow
  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: Mercadante, Thomas F
  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 - Thoughts Needed

[JOE TESTA]

Tracy, where do you draw the line of when you should include 
them in the table as atrributes?
 
Since new coverages could be added at any time, i'd make them 
their own child entity.
 
I wouldnt concern yourself with i/o, if its indexed correctly 
its so fast noawadays, i'm not sure you could tell the difference.
 
Anyone else's thoughts?
 
Joe
 
>>> [EMAIL PROTECTED] 06/25/01 01:15PM 
>>>We are looking to redesign our current system and a question 
popped up that Iam struggling with.  (our business is auto 
insurance).  Each vehicle on apolicy may have 1 or many 
coverages.    For example, comprehensive, collision,towing, 
property damage ...  A couple of things: 1-  some coverages will 
notapply because they are state specific.   2 - some coverages 
will not be chosenby the insured.  Therefore, should I have a vehicle 
table with each coverageand its premium as separate specific columns or 
should I add a coverage tablewhich will contain the coverage and 
premium.  Under the later example if I have10 coverages and I want to 
print them on the insured's declaration page don't Ihave a possible 
performance problem with up to 10 i/os.  Where as with thefirst example 
I only have 1 read but possible space wastage.   I know in 
1stnormal form you should remove repeating groups, in my case is a coverage 
arepeating group?   Where do you draw the line?  For example, 
clients may havemultiple phone numbers although I don't see many examples 
where the phonenumbers are split into another table.  Please share any 
thoughts that may makemy decision easier.  ThanksJust an fyi, 
in our current design the coverages have been stored with 
thevehicle.-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Tracy 
Rahmlow  INET: [EMAIL PROTECTED]Fat City Network 
Services    -- (858) 538-5051  FAX: (858) 538-5051San 
Diego, California    -- Public Internet 
access / Mailing 
ListsTo 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from).  You mayalso send the HELP command for 
other information (like subscribing).