asked
Complex Data Model Design
I am trying to design a set of tables to do with Insurance and ran into a bit of a stumbling block with the data model as follows: **Tables:** tblInsurancePolicy tblInsurancePolicyHolder When it came to recording insured assets I am dealing with something along the following lines: **AssetTables:** tblVehicle (Motor Policy) tblProperty (Home Contents Policy, Landlord Insurance etc) tblPerson (Health Insurance, Travel Insurance) tblPet (Pet Insurance) tblBusiness (Professional Indemnity, Public Liability ... other commercial insurances) I have been around in circles with this - basically each policy will apply to an asset of a certain type, and each asset has different attributes. I am unsure how to structure this correctly - as I need to reference the asset type covered from the policy. Obviously, I am trying to think about how I query this too and don't want to have to write complex dynamic SQL that queries a particular asset table based on a type in a policy table field - It would be a nightmare to maintain. Appreciate some help here or a pointer in the right direction - Is there another way to look at this data I am just not seeing?
Implementing a supertype/subtype design can be "challenging"! It will often boil down to how many different subtypes you have, how different are they between the subtypes and how are you likely to query them. If you have very few subtypes and the attributes of them are very similar, then I've seen designs that handle that well in just one table, with nullable columns for the attributes. Start to get more subtypes though, or very different attibutes then it's worth splitting each subtype out to it's own table with a 1-1 relationship back to a 'parent' supertype - you can enforce RI very easily here with check constraints and foreign keys to make sure the subtype data goes into the 'right' table. You don't need dynamic sql to handle the queries, but you will have conditional outer joins - making the query, and the execution plan, larger. But again that's only if you need to generically query the data for 'all insurance types' - if you know which type (for example the calling application has some context), then you can write specifc queries targetting motor insurance, home insurance etc.
Kev, thanks for the answer - I went down the route of creating multiple subtype tables all linked back into 1 parent table. I am the application to drive the JOIN types, which is ok, it just means more application side coding, or lots of GET stored procedures which means a change in the parent table has to factor up through lots of code.
17 People are following this question.
