Click here to Skip to main content
15,885,366 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all,
Little question, I know an answer, but I'm open to suggestions or alternatives (or confirmation). My solution sounds a bit complicated, but I can't really think of anything else that's flexible enough.

The problem is this, I have some entity, MyEntity, of which each record can be combined with one or multiple records of MyEntity.
For example, I have A, B, and C.
Possible combinations are now:
A
B
C
A, B
A, C
B, C
A, B, C

Now here's the challenge, A, B and C have a percentage, say 100%.
However, when A is coupled with B their percentages change, for example A 40% and B 60%
A = 100%
B = 100%
C = 100%
A = 40%, B = 60%
A = 40%, C = 60%
B = 45%, C = 55%
A = 20%, B = 30%, C = 50%

There is no logic in the percentages, it's just a given percentage.
However, the percentages may change over time and an additional D may be added, changing not only the possible combinations, but also the corresponding percentages.

So I'm looking for the easiest way to model this in SQL Server.

I'm thinking:
MyEntity (Id, Description)
MyEntityCombination (Id, Description)
MyEntityCombinationValue (Id, MyEntityCombinationId, MyEntityId, Percentage)

And then comes the next challenge, in my C# code I get B and C and I now need to get the corresponding percentages for B and C (45% and 55% in the example).
I'm guessing this should be fairly easy using Entity Framework and LINQ.
Something like:
C#
var entities = new[] { bId, cId };
var percentages = context.MyEntityCombinations.Where(c => c.Values.Count() == entities.Length && c.Values.All(v => entities.Contains(MyEntityId)).Values;
var bPercentage = percentages.Single(p => p.MyEntityId == bId).Percentage;


What I have tried:

I've thought about it, just curious for (easier) alternatives.
Posted
Updated 17-Jun-18 8:48am
v2
Comments
[no name] 17-Jun-18 15:01pm    
If you're tracking percentage changes "over time", then you need to add a "time element" to your model.

(And I think the "Id" in MyEntityCombinationValue may be redundant; EF supports "compound keys".)
Sander Rossel 17-Jun-18 15:23pm    
I'm not looking for changes over time, just the best/easiest way to store the percentages.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900