Complex many to many structure

[blom0344]

I'm trying to resolve a complex many-to-many model involving Units and Contracts:

1. Both tables store dimensional and fact related data
2. A unit can have 1 contract (which may be expired or not) or no contract at all
3. In most cases one contract relates to multiple units
4. In the rarest of cases 1 unit could have 2 valid contracts at the same time

My first inclination is to write a complex union covering data from both tables and adding 'correction factor' fields and indicator fields for all possible sets using this as a 'combined' fact..

Any thoughts / alternatives?

Ties Blom

 

[johnherman]

Well, a unit can have zero or more (max 2) contracts. A contract can have zero or more units. There should be a bridge/union table, having the primary keys of each unit and contract and could possibly benefit from having the effective dates of the contract, thus simplifying searches for the correct contract. The dates could be omitted if contract expirations are rare. I am pretty sure I would not build a "grand union" table of all the facts and dimensions, only of the most searched fields. My recommendation:

Unit-Contract Table
Unit-Contract-ID (surrogate) number, primary key, indexed
Unit-ID (surrogate key of Unit table) number, indexed
Contract-ID (surrogate of Contract table) number, indexed

optional fields:
Contract-Start-Date
Contract-End-Date
Natural Key of Unit table, indexed
Natural Key of Contract table, indexed







The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[blom0344]

Yes, the bridge table exists and finding individual combinations is not the problem. The issue lies with the statistic analysis that needs to be performed. The contract may store a value which obviously relates to n units. Simply aggregating this value would yield n times the values when units are included.
So somehow I need to add correction factors to compensate for the overcounting.
The opposite may also occur. With 2 valid contracts the unit measures will become bloated.

Also the expiration issue is more complex then I thought , as in some cases contracts may neither be active nor expired, but are of the future kind..

My idea would be to move all facts to bridge object with the needed correction columns adding 'dummy' unit - contract combo's for units without a contract (using a dummy value for the contract key). This way I can use a regular join for the unit dimension and after adding a dummy record for the contracts do the same with the contact dim..

Ties Blom

 

[johnherman]

I would create a separate datamart/set of tables to support the statistics. Denormalized and without indexes, as the stat package probably does its own data structuring. Unless disk space is a major issue, then your solution would appear to be the best available.

The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[blom0344]

Yes, a seperate structure for stats is an alternative. Unfortunately the first implement is directly against the transactional tables (I know.., I know) , so I remodel using views.
However, we are very fortunate with the amount of data involved (related tables storing only a few thousand records max.)

Ties Blom