Query specific key fields from x-number of tables?

Question

I'm building an insurance database and I need some help with best practice on fast quering data.

Let's say I have three insurance types ... Car, Home and Travel.

Common for all three types, I have the "Insurance" table:

[dbo].[Insurance] (
    [InsuranceID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [CustomerID] [uniqueidentifier] NOT NULL,
    [CreatedDate] [datetime] NOT NULL
) 

And then I have the three insurance types:

[dbo].[InsuranceCar] (
    [InsuranceCarID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [InsuranceID] [uniqueidentifier] NOT NULL,
    [Model] [uniqueidentifier] NOT NULL
) 

[dbo].[InsuranceHome] (
    [InsuranceHomeID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [InsuranceID] [uniqueidentifier] NOT NULL,
    [Address] [uniqueidentifier] NOT NULL
) 

[dbo].[InsuranceTravel] (
    [InsuranceTravelID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [InsuranceID] [uniqueidentifier] NOT NULL,
    [Destination] [uniqueidentifier] NOT NULL
) 

All three insurance types have an "key search field" that I use for quering an insurance - in "Car" it is "Model", in "Home" it is "Address" and in "Travel" it is "Destination".

So the way I have solved it, is to create a "_Search" column in the "Insurance" table ...

[dbo].[Insurance] (
    [InsuranceID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [CustomerID] [uniqueidentifier] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [_Search] [nvarchar(MAX)] NULL
) 

... and when any of the insurance type table rows is created/updated, then I copy the "key seach field" from the incurance type table into my "Insurance" tables "_Search" column (using trigger).

This way I can quickly query any key fields from the three insurance type tables by quering the "_Search" field in the "Insurance" table.

Problem is that it takes up more space in the database to do it like this.

Any better way to do this? Best practice?

Thanks.

Answer 1

Understand the problem first, worry about performance later.

This question is too broad and not a good fit for this venue. However, as someone who works in the property and casualty industry I feel compelled to say... pump the brakes on this for a second. If this is a hobby project... there are hobbies that are more relaxing.

This is not an easy undertaking, and I can say that I've seen many attempts at this, and they all get something wrong, especially when it comes to the problem you're describing - having multiple types of insurance contracts.

Ultimately that is what you are modeling here - a contract. Insurance contracts have rules, they can be altered, premium must be calculated and balanced correctly, and they are highly regulated.

So the best practices here are:

1. Enforce as many contract provisions as possible through the data model
2. Anything that cannot be enforced through the model must be enforced through transaction logic
3. Make sure you are aware of the reporting and audit requirements of your jurisdiction

Please read this, then refer to the diagram below. It's a head start.

This model doesn't even being to address some major concepts you will need address, such as:

1. Coverages/insurance lines/reporting codes
2. Governing jurisdictions
3. Limits/deductibles
4. Forms/endorsements
5. Managing customer information across policy types
6. Premium and policy accounting

As an aside, "fast queries" and GUIDs rarely occur together. You'll also notice they are absent from the above data model.