Insurance product data model and concepts

[Reinier]

Insurance data model and concepts

The insurance software branch uses a lot of generic Novulo concepts and applies them in the context of insurances. This article aims to give an overview of the tables that are used in insurance components, and to which purpose.

In insurance software, the most basic goal is to register an insurance contract: a policy, containing coverages, and sometimes part of a package. We start by explaining the concept N_Product.

N_Product

N_Product is the concept used for products. Depending on your composition, a product can be used in a variety of ways. It could be sold, purchased, billed, produced, delivered, kept in stock, used in an insurance contract, and so on. It always has a name, a code and a type.

In insurance, the product configuration determines the composition and properties of the eventual insurance contract.

A product has one of three ‘Insurance types’: Package, Policy or Coverage

• For packages, the generic type of the product must be ‘kit’, so you can make compositions with policy products underneath them
• For policies, the generic type is also ‘kit’, for compositions with coverage products
• For coverages, the generic type is ‘product’

N_SalesPriceListLineitem

After configuring your product, you want to configure how you sell it. A product can be sold in multiple ways, with multiple prices. This is configured in the concept N_SalesPriceListLineitem. This table links a product to the N_SalesPriceList table, so you can determine a separate price for your product for each price list. You could have a price list for private sales or business to business sales, for instance. Look here for more info on N_SalesPriceListLineitem.

In insurance, the distinction between price lists is usually the insurer. One policy can be offered by multiple insurers, and each insurer determines their own premium rate (=price).

Now, it becomes relevant what role your company plays in the insurance branch, because the configuration of an insurance sales price list item is highly dependent on this role.

Three types of insurance companies are insurers, agents and proxies. There are more, but these three paint a clear picture of product configuration differences.

• An insurer is in full control of the pricing of their products and configures the required calculations for their premiums.
  • Usually, an insurer has only one sales price list item per product; themselves being the insurer of that item.
• An agent is a company that sells insurances for multiple insurers.
  • For each product they sell, they have a list of sales price list items corresponding to all insurers they work with. They don’t calculate premiums themselves, but follow the insurers, resulting in a simpler “bare-bones” configuration.
• A proxy is a company that, like agents, sells insurances for multiple insurers, but where an insurance sold by an agent always needs to be approved by the insurer, a proxy has received the authorization to independently sell an insurer’s products.
  • This results in a list of sales price list items per product, correspondig to all insurers they work with, including premium calculations.

N_System and N_SystemChange

Now that we know how to configure the composition and premium determination of our product, we can start registering insurances.

An important aspect of insurances is that each insurance is unique and has a unique identifier: a (package number,) policy number and coverage number. If a customer has 4 insurances of the same sales price list item, you still want to seperately register each of those insurances, as they all have their own starting date, length, payment terms, etc.

For registering unique instances of a product, we use N_System. In it’s most basic form, N_System is a record that belongs to an N_Product and has a unique identifier.

However, one more important aspect of insurances is that their details can change. An insurance may start the first year with a premium rate of €100,- per month, but the second year could have a raised premium rate of €105,-. Because we want to keep track of every detail of every change, we introduced the concept N_SystemChange.

We use the concept N_SystemChange to actually register a policy contract.

To visualize the relations between N_SystemChange and N_Product, have a look at the following graph:

Bundles

In practice, a policy contract always has multiple levels of products. A policy is always accompanied by at least one coverage, and sometimes, multiple policies are bundled in one package. Packages, policies and coverages are all separately configured products, along with their sales price list items, bundeled together.

The following graph gives a simplified overview of how the N_SystemChange, N_SalesPriceListItem and N_Product tables are connected when taking these bundles in account.

This graph is simplified, because the relation between multiple products, sales price list items and system changes are not directly connected, but through linking tables; resulting in many-to-many relations. This allows you to have packages with multiple policies, but also to re-use policies in multiple packages.

The following graph zooms in on these linking tables.

• With N_ProductComposition you create bundles of packages, policies and contracts. These make up the possible bundles you can sell;
• With N_SalesPriceListItemComposition you create priced propositions of bundles to sell;
• With N_SystemChangeElement you register contracts based on those prices bundles.

For example: you can have a health insurance policy without a dental coverage, with a “basic” dental coverage, or a “premium” dental coverage.

N_Question and N_QuestionLink

Now that we can register an vast variety of contracts based on our product configuration, one remaining topic is registering the correct properties of those products. Two products can be significantly different in which information is required to store.

For example: a life insurance policy wants to register whether the insured person smokes or not, where-as this is completely irrelevant for a car insurance policy.

To ensure you can store the correct information for all products, we use the N_Question and N_QuestionLink tables.

• N_Question defines a property. It contains the name of the property, how it should be answered (is it a number, text, yes/no, etc.), and more;
• N_QuestionLink links a property to a product or sales price. This again creates a many-to-many relation, so we can reuse a question in multiple products or sales prices.

We also make a difference between properties linked to products versus properties linked to sales prices. Initially, you can configure all the properties that are relevant to a product at the product level, but usually every insurance company has their own subset of properties that are relevant to their propositions, so you also link the properties at the sales price level.

• To properly separate the links between questions and products vs. questions and sales prices, we introduced the inheriting tables N_QuestionLinkForProduct and N_QuestionLinkForSalesPriceListItem

N_Answer and N_SystemChangeActualAnswer

With questions you define which properties are included in a product and its contracts. Now the properties still need a place to store their values: the N_Answer concept, and more specifically, N_SystemChangeActualAnswer when we store the values that belong to contract.

• When a contract is generated, we generate a N_SystemChangeActualAnswer record for each N_QuestionLinkForSalesPriceListItem

The answers table has various fields to store the correct value: a text field, a number field, a date field, etc.; the applicable field is chosen based on the configuration of the question.

With this set-up, a contract can store all general information (like a contract’s effective date, the owner, etc.) in it’s own N_SystemChange table, and all product-specific information in the N_SystemChangeActualAnswer table.