Considerations for single or multiple factor rate tables

Rate table definitions can have one or more factor columns. Consider using multiple factors only when all factors represent the same data. For example, in the sample data, the AutomaticRenewalCap rate table in the Personal Auto line has two factors: Amount and Percent. Different business logic (in rate routine steps) is associated with each factor. However, both factors represent the same data, the renewal cap, presented in one factor as an amount and as a percentage in the other.

If factors represent different data, consider modeling that data as a parameter. For example, you have a spreadsheet with one parameter and seven factors for different data: one for each Canadian province. The following table provides an example:

In the example above, a rate routine that retrieves the factor for all provinces requires an IF statement with seven conditions, one for each province. Consider modelling the province as a parameter instead of as a factor. However, if this approach works better for you, then you can simplify the rate routine with a function. The function takes the province as a parameter and does the rate table lookup to get the correct return factor. If rate table size is an issue, consider creating separate rate tables. For example, each province has its own rate table.

When the province is modelled as a parameter instead of as a factor, the rate routine is simpler because you only have to pass in the lookup province. The following table provides an example:

The rate routine requires only one statement to access the factor. You improve the readability of the rate routine but increase the number of table rows.

Database normalization can be helpful in designing rate tables. Consider normalization if you denormalized a rate table because the legacy system could not process the normalized tables fast enough. For example, your vehicle risk factor is equal to the year factor multiplied by the model factor. Your previous implementation used a single rate table with rows for each year and vehicle combination. If there are five years and five models, the rate table contains 25 rows. Using normalization, you can implement this using two five row rate tables. One table contains the risk factor for the year and another table contains the risk factors for the model.