RE: Data Modeling - The Vehicle/Insurance Scenario

Tracey,

Sorry I didn't keep the original message - seems I've only got about 10K of space on the mail server - I have to delete almost everything immediately. Anyway, your question intruiged me. Most everybody thought you should have at least three tables, I suspect it's even more.

Here's my quick read (I had to extrapolate a bit on your info to come up with a first cut here):

Policy_Number is Unique - if not, then the combination of Policy_Number and Customer_Number should be Unique.
Customer may have many Policies
Policy may have many Vehicles
Vehicle may have many Drivers
Coverage_Type is Unique
Vehicle is Unique (Surrogate of Make/Model/Year) Coverage may or may not apply to Vehicle Coverage may or may not apply to State
Coverage may have many Deductibles

There are all sorts of things I don't know about your business here, but you mentioned the premium was different for each vehicle. Generally that is a derived value, calculated using a standard premium base amount combined with factors like deductible amount, age/sex of driver, miles driven, repair history of the vehicle, and so forth.

Given all this, here's my take on a possible table scenario:

|-------------------+-----------+--------------------------------------------------------------------|


| Entity Name       | Entity    | Primary Keys                                         

|              |

|                   | Type      |                                                      

|              |

|-------------------+-----------+--------------------------------------------------------------------|


| Coverage_Deductibl|Dependent  |DEDUCTIBLE_KEY,COVERAGE_KEY                           

|              |

| e                 |           |                                                      

|              |

|-------------------+-----------+--------------------------------------------------------------------|


| Coverage_Master   |Independent|COVERAGE_KEY                                          

|              |

|-------------------+-----------+--------------------------------------------------------------------|


| Customer_Master   |Dependent  |CUSTOMER_NUMBER,POLICY_NUMBER                         

|              |

|-------------------+-----------+--------------------------------------------------------------------|


| Policy_Driver     |Dependent  |POLICY_NUMBER,VEHICLE_NUMBER                          

|              |

|-------------------+-----------+--------------------------------------------------------------------|


| Policy_Master     |Independent|POLICY_NUMBER                                         

|              |

|-------------------+-----------+--------------------------------------------------------------------|

||POLICY_NUMBER,VEHICLE_KEY,COVERAGE_KEY,DEDUCTIBLE_KEY,VEHICLE_NUMBER|
|-------------------+-----------+--------------------------------------------------------------------|


| State_Coverage    |Dependent  |COVERAGE_KEY                                          

|              |

|-------------------+-----------+--------------------------------------------------------------------|


| Vehicle_Coverage  |Dependent  |COVERAGE_KEY,VEHICLE_KEY                              

|              |

|-------------------+-----------+--------------------------------------------------------------------|


| Vehicle_Master    |Independent|VEHICLE_KEY                                           

|              |

|-------------------+-----------+--------------------------------------------------------------------|



Minimum attributes identified are:

Coverage_Deductible DEDUCTIBLE_KEY, COVERAGE_KEY, DEDUCTIBLE_AMOUNT
Coverage_Master     COVERAGE_KEY, COVERAGE_TYPE, STANDARD_PREMIUM
Customer_Master     CUSTOMER_NUMBER, POLICY_NUMBER, CUSTOMER_NAME, STATE
Policy_Driver       POLICY_NUMBER, VEHICLE_NUMBER, DRIVER_NUMBER,
DRIVER_NAME, AGE, SEX
Policy_Master       POLICY_NUMBER, CUSTOMER_NUMBER
Policy_Vehicle      POLICY_NUMBER, VEHICLE_KEY, COVERAGE_KEY,
DEDUCTIBLE_KEY, VEHICLE_NUMBER, MILES_DRIVEN
State_Coverage      COVERAGE_KEY, STATE
Vehicle_Coverage    COVERAGE_KEY,VEHICLE_KEY
Vehicle_Master      VEHICLE_KEY, MAKE, MODEL, YEAR

I think you can get some real flexibility, maintain some pretty solid data integrity ( no update or delete anomalies) and have some pretty quick access to a variety of reports. Okay, I'm ready to take my lumps now.

Regards,

David A. Barbour
Oracle DBA, OCP
AISD
512-414-1002

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: DBarbour_at_austin.isd.tenet.edu

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