I'll get you started. The assumption is that you want to
build a smart key for indexing and easier categorization (without
having to do a deep dive into the details). Think about the
use cases for this - the cautionary tale is that your business is
going to continue to evolve and your policy numbers will not have
that ability. For all sorts of reasons (some of them legal),
you can't swap out policy numbers in mid-flight. But you can
do some fundamental sorting...
Things to consider/practices I have seen used at various
1) The first two digits are the origination year. Be
careful to make it character, not numeric!
2) The next two digits (maybe 3) are the product number, with
maybe even a family of products as the first digit (is this a life
insurance policy, or an annuity - then, a term policy or universal
3) A suffix that helps document how many policies exist for this
All of these need to be considered with the business rules that
will go along with them. For example, if you have a term life
policy that lapses and then is reinstated - how do you handle
that? Can you pass ownership from the original policyholder
to a subsequent owner? Do you cancel and rewrite every time,
or are there instances where you would need to retain the original
policy number (and how will that mess up your smart key)? If
you assume that a customer's newest policy ends in 'D', that
doesn't necessarily mean there are/were 4 policies for this
customer (maybe some of them never made it out of
There are other ways you could embed additional information
(underwriting method (was it autoscored or was it done manually),
group vs individual policy, parent/child relationships) - but think
hard about what this will look like in the future (consider history
and how well your strategy would have held up over the years and
what events happened to cause the strategy to evolve).
I'm sure others have their experiences to add.