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_Prem Pip_Ded Pip_Limit Pip_Prem
1 97 Jeep 1000 500 25.25 50 10 "NULL"
"NULL" "NULL"
2 99 Truck 7000 1000 20.50 "NULL" "NULL"
"NULL" "NULL" "NULL"
3 98 Car 5000 1000 15.00 0 30
250 100000 35
Method #2
Vehicle Table
Seq Year Type Annual_Miles
1 97 Jeep 1000
2 99 Truck 7000
3 98 Car 5000
Coverage Table
Seq Covg_Id Covg_Ded Covg_Limit Covg_Prem
1 A(Comp) 500 "NULL" 25.25
1 B(Towing) 50 "NULL" 10
2 A 1000 "NULL" 20.50
3 A 1000 "NULL" 15
3 B 0 "NULL" 30
3 C(Pip) 250 100000 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
--------------------------------------------------------------------
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: 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: 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).
