×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Complex many to many structure

Complex many to many structure

Complex many to many structure

(OP)
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
 
 

RE: Complex many to many structure

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

RE: Complex many to many structure

(OP)
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
 
 

RE: Complex many to many structure

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

RE: Complex many to many structure

(OP)
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
 
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close