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?


1 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.

enter image description here

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.

  • Before anyone says "the discriminator should not form part of the key of the entity": 1. Normally yes. 2. In this case it is required once you start modeling Coverage, which are limited to certain insurable entities.
    – user212533
    Commented Jul 27, 2020 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.