flexible db design

[george]

Greetings to everybody,

I am currently working on a insurance project. I use table
tblPolicies with a triple primary key: BranchID, CompanyID
and PolicyID. Included in the table are also a few more
fields with policy details like IssuedOnDay, ExpiryDate,
RenewalDay, and YearlyPremium

These details are common for all policies regardless of
the Branch that the policy belongs to (ie Life Policies,
Motor Policies, Fire Policies etc). However there are some
other details pertaining to each policy which are Branch
specific and must be recorded separetely.

The obvious solution is to create many tables like
Life_PolicyDetails, Motor_PolicyDetails,
Fire_PolicyDetails etc to record all these Branch specific
details. However this impplies that everytime the client
introduces a new Branch I should have to intervene to the
db design to create a new What_PolicyDetails table for
this Branch and then relate it to the tblPolicies.Of
course this is not something desirable.

Is there a smarter solution to this situation? Thanks in
advance, george.

 

[Jeff Boyce]

George

As you've anticipated, embedding meaning in the tablenames is not a good
relational design.

Why would you need a table specific to a Branch? Couldn't you just include
a BranchID field in the X_PolicyDetails table, to show which Branch "owns"
the details?

Check up on the topic of normalization to see what additional ideas it can
offer.

 

[george]

Jeff,
thank you for your reply. I have already done this in the
table tblPolicies. But concerning the Branch specific
details I do need, I believe, a separate table.

Example: In the Fire Policies some policy specific details
would be AgeOfHouse, TypeOfConsruction, Area, Contents,
etc whereas in the Motor Policies the details would be
AutomobileBranch, HorsePower, AutomobileColor etc. How do
you deal with a situation like this?

thanks again, george

 

[Jeff Boyce]

George

If you are willing, step back from what you may have already decided and
describe the entities and relationships you are working with in a little
more detail.

For instance, you've described a policy table (tblPolicy) that sounds like
it is in a one-to-many relationship with a tblBranch, a tblCompany and a
?!tblPolicy?! You've also described what sounds like a PolicyType (Life,
Auto, ...), and indicated that each of these types have additional
attributes that don't match other "types" attributes.

The latter seems like a situation calling for a one-to-one relationship.
That is, you might "register" all policies in a policy table (as it sounds
like you have), and use a one-to-one relationship for details on the types.

I'm not very clear on how you will need more "type" tables -- don't you sell
a specific "stable" of policy types? Are you saying there's a great deal of
change in what can be insured?

 

[george]

Jeff,

again I must thank you for your answer. I use tblPolicies
as a main table. BranchID, CompanyID are lookup fields
which get values from tlkpBranches and tlkpCompanies. A
rough idea of what I have in mind is found at

http://www.databaseanswers.org/data_models/insurance_broker
s/index.htm

As you very well suspected the different Branches of
insurance are not very many, 10 to 15 in fact. However an
average insurance agent works with only 4 or 5 among these
and for these 4 or 5 I make provision, as for the rest...
oh well... talking to you I realize that finally I have to
create tables for most of them or all! There is no way
around this, maybe this is the smartest thing to do.

thanks,take care, george

 

[Jeff Boyce]

George

I'm glad to hear you are quite confident, but I have some reservations,
based on your description/discussion.

I would urge you to re-post to the 'group with a more complete description
of your proposed table structure -- see what other folks think...

 

[Jamie Collins]

embedding meaning in the tablenames is not a good
relational design.

Good advice. Only this week I saw a post from you in this newsgroup
suggesting table names with a 'tbl' prefix. Glad you've now seen sense
<g>.

Jamie.

--

 

[Jeff Boyce]

Perhaps I mis-spoke -- embedding data in tablenames is not good design.
What use would tablenames be without some form of meaning?!<G>


Jeff Boyce
<Access MVP>