SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Modeling with M:N and avoiding duplicates


Modeling with M:N and avoiding duplicates

Author
Message
ttdeveloper
ttdeveloper
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1981 Visits: 482
Good Day guys.

I have attached a screenshot of my current dilemma. Essentially, I have two sets of tables.

One with an org structure and the other with Sales by accounts.

I am trying to resolve this M:N situation that I have and generate a way for queries to be run with dups.

Using the attached, we can see that multiple associates manage the same account. If I wanted to pull up their sales, its pretty straight foward by just joining to the account table and looking for the individual sales.

The problem I have is that when I go to rollup the sales values to Assistant Manager and above level, the sales values are duplicated and in my example i run the risk of reporting $600 in sales for account 123 and $1200 for account 456 when reporting at AM level.

I know i need to stick some kind of table in between to resolve the M:N - ie new table say [Org structure sales] - with three columns

org_str - PK
Sales fk
org fk

however this scenario still produces dups for me.

Any help is appreciated.
Attachments
MODEL.JPG (46 views, 42.00 KB)
RonKyle
RonKyle
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26959 Visits: 4467
A column to identify the portion of the overall value would seem to work. Not enough information presented to be sure that's the ideal solution.



ttdeveloper
ttdeveloper
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1981 Visits: 482
A column to identify the portion of the overall value would seem to work. Not enough information presented to be sure that's the ideal solution.


Thanks for getting back. Not sure I understand what you mean by this statement.

What other information do you need to help you understand my situation? Sorry if I wasnt clear.

I need to resolve the M:N relationship between
Account MANAGED BY multiple associates
and
Associates MANAGING multiple accounts.

My current setup wont allow me to report correct sales for AM - C,
Manager - B and Director A.
RonKyle
RonKyle
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26959 Visits: 4467
Add a column to the child table with the proportion broken. One line would have 0.6 and another 0.4 for example. The total is multiplied by these amounts. As long as each separate account totals 1.0, there should be no problem. Let me know if that works.



Tom Thomson
Tom Thomson
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93247 Visits: 13632
A solution, perhaps not the best one, is to use a query like

WITH DistinctOrg as (
select distinct ID, Director, Manager, AssistantManager, Region, Account from OrgStructure)
select D.AssistanManager, SUM(S.Value) as TotalValue
from DistinctOrg D inner join SalesByValue S on D.Account = S.Account and D.Region = S.Region ;



Rolling up to a higher level -
for Manager: replace all occurrences of DistrictManager in the query by Manager (and, if you want to be tidy, make sure Manager occurs only once in any select list by eliminating one occurrence when Manager is duplicated)
for Director: starting with the Manager version, replace all occurrences of Manager by Director (and clear up repeats of Director in the select list).
Of course if the version for manager can simply be the version for assistant manager if each manager controls only one assistant manager, and similarly the version for director isn't needed if each director controls only one manager, but it would be foolish to assume that you can get away without the additional versions - organisations change from time to time.

Or you can create the three auxiliary tables as ordinary tables instead of generating them on demand using CTEs if the performance difference outweighs the table space difference.

Tom

ttdeveloper
ttdeveloper
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1981 Visits: 482
Hey Ron
While this works as a solution for a one off run. This is going to be difficult if not impossible to implement in a model because it means everytime we add a new associate, this "additional column" will need to be manually updated across all affected accounts and org levels.
Tom Thomson
Tom Thomson
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93247 Visits: 13632
RonKyle (3/24/2015)
Add a column to the child table with the proportion broken. One line would have 0.6 and another 0.4 for example. The total is multiplied by these amounts. As long as each separate account totals 1.0, there should be no problem. Let me know if that works.

It might be better to hold the count of associates for an account and assistant manager pair, and divide, since the count can be held exactly as an integer (maybe a tinyint) rather than as an approximation potentially occupying a little bit more store. And if managers can have multiple assistants and directors can have multiple managers a divisor would have to be held for each of those levels too.

Tom

ttdeveloper
ttdeveloper
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1981 Visits: 482
Hey Tom
Thanks for getting here. The Cte definitely works for one off queries. This model I am looking to fix however is for a DataWarehouse and I need some sort of a more permanent solution.

The DW feeds an SSAS cube and there are requirements to allow drilling at any given time from Director to Rollup so essentially i need all values calculated and preloaded.

From a modeling standpoint, my ideal situation (which I am currently trying to map out now) is to resolve the M:N relationship by 1,2,3 Normal forms and creating the model from there.

I posted this to see if anyone had any ideas specifially from a modeling standpoint.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search