Multivalued Dependency removal in OLTP database

Question

question

Torch asked Jun 11, '10

Multivalued Dependency removal in OLTP database

Hey All,

I have a fairly interesting problem because I'm a bit stumped on some business process / functional process requirements in order to make my database work.

I'm currently working at an Insurance company, and they have policys that are sold to the customer. The nitty gritty of it all is that I am redesigning the database for the OLTP application. I have most of my relationships taken care of, i.e. how someone in the system will arrive at the rates for the policies (which are dependent on all of those relationship conditions). I seem to get stuck at the following:

Superset to Subset Bandings -> Rates -> Group# -> Terms. To explain, for every banding, there is a $$ Rate Amount. All Rates belong to a specific Group# and those rates can also be affected by which terms are chosen for the policy. I have a multivalued dependent relationship in a table that goes RateID, BandID, GroupID, TermID RateAmount.

I am trying to Normalize this a bit more. Does anybody have any suggestions for splitting up the tables and maintaining the business logic behind it. It's got me slightly stumped and I could use an outside opinion.

sql-server-2005 table-valued business-logic-layer

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-06-11T11:51:39Z

Grant Fritchey answered Jun 11, '10

I work at an insurance company too, although we mainly work in property and this looks a bit different than our sort of layout. So terms make up Groups, groups make up rates and rates make up bandings? If so, You should have to have a table that contains foreign keys from all of them. Simply listing a term should make it possible to derive the group, rate and band through relationships up the chain. Unless I'm completely misunderstanding the issue.

If I'm off, what might help is to publish a more complete structural example so people can see how you have it currently laid out.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Torch commented · Jun 11, 2010 at 11:57 AM

Not too far off, but essentially I currently have it in a table with the related foreign keys, however this keeps the table in 3NF I'm trying to achieve 4th or 5th because I wish to avoid update anomalies within the chains, hence the removal of multivalued dependencies. I've thought about creating a new set of tables to help enforce the relationships with fabricated keys for the logic but I'm stumped because I really don't know what to put in them at thist ime.

0 ·

question