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_Vehicle |Dependent ||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: [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).
