0

Pictured is the visual representation of tables I'm about to describe. From a high level in plain English, there are 3 levels:

  1. Capabilities (Communication, Industry, etc.)
  2. Competencies (Objectivity, Questions, etc.)
  3. Skills (I listen and read objectively, etc.)

Capabilities contain a number of competencies and competencies contain a number of skills.

Then there are Users and there are the responses Users give to the Skills (the circled number choices).

So I designed a pretty straightforward set of tables for this described below. I tried to only include fields necessary to understand and answer my question to keep it simple.

  • Users: UserId, Username
  • Assessments: AssessmentId, Title
  • UserAssessments: UserAssessmentId, UserId, AssessmentId

  • Capabilities: CapabilityId, AssessmentId, Title
  • Competencies: CompetencyId, CapabilityId, Title
  • Skills: SkillId, CompetencyId, Text

  • UserSkillResponses: ResponseId, UserAssessmentId, SkillId, Response

Okay so it gets a little more complicated with the last UserSkillResponses table. The "Response" column is just an integer for the numbered circle the chose. But it has two foreign keys, each to a separate table. Originally I tried to use Entity Framework in my project and came to the conclusion that one of three things must be true:

  1. EF simply doesn't handle this scenario well... at all
  2. I'm simply inexperienced with handling advanced scenarios like this in EF
  3. I didn't design the tables in a natural or proper manner which complicates working with EF

1 and 2 can be a bit subjective, but it's 3 that I want to ask about here, which I believe is a reasonably objective topic. Being the expert that you are: do you see a glaring issue with this table design, or does it look exactly like you'd expect?

I would like to get some feedback whether I'm on track with the database design because if I'm not then I would love nothing more than to admit it and make corrections. But if there's no significant problem with the db design that's causing my EF pain then I'm inclined to believe EF is not a good fit for my project.


EDIT: An assessment can have any combination of capabilities. I initially considered using a many to many relationship for this instead of putting AssessmentId on the Capabilities table, however a client may make edits to capabilities on their assessments which should not affect other clients' data. So I didn't want every assessment pointing to the same capability record as editing its title would then change the title for everyone. So instead I use a set of capabilities as a template and copy them to new records for each assessment. Then if a client changes something, I can apply the change to only that clients' assessments. So in a sense it's almost like getting a receipt from a grocery store where the records for your receipt are not the actual item records but a copy of them, and in this way they can modify prices and such for you without changing the actual item records.

EDIT 2: Here is an example of the kind of EF statement I was having trouble with. To understand the issue presented here, think of a hierarchical tree where items would normally have 1 parent and multiple children; but in the case of the Responses here it really has two parents. When you think of Responses in isolation it's easy enough to flip it around and think of UserAssessments and Skills as children, but when put in context with other tables you can no longer do that. So I think that's why it looks deceptively "usual" at first glance, but I couldn't figure out how to model this very well with EF.

There might have been some additional configuration code somewhere that allowed the code below to work properly b/c at first glance it doesn't look proper to me now, but it did work. The only problem was that it was very slow. And when I looked at the sql it generated I immediately made the decision not to go forward with EF any longer. I've spent so many years learning to write good sql so it just made more sense for me to do that then to spend more years trying to learn how to make EF write good SQL.

userAssessmentQuery = AppDbContext.UserAssessments
    .Where(o => o.UserId == userId && o.Assessment.Title == assessmentTitle)
    .Include(o => o.User)
    .Include(o => o.Responses)
    .Include(o => o.Assessment)
    .ThenInclude(o => o.Capabilities)
    .ThenInclude(o => o.Competencies)
    .ThenInclude(o => o.Skills);

Skills Assessment

11
  • So, the general design, many-to-many tables like that, is pretty standard stuff. Lots of people can have lots of skills. Just makes sense. I've seen EF handle that kind of thing just fine. Guessing, either you don't have well defined foreign keys, or, you're doing something funny in EF. I can't say which, because I can't see any of the code, database or EF. Oct 7, 2021 at 11:32
  • 2
    Looks to me like you have a design issue here: Capabilities shouldn't have a foreign key to anything because it exists on its own. UserAssessments doesn't need a separate primary key, it should use its two foreign keys as a compound primary key. UserSkillResponses design also doesn't seem to need a separate primary key: there should be exactly one response per assessment and skill combination, ergo it should instead be called UserAssessment_Skill and should have a compound primary key. Oct 7, 2021 at 12:26
  • @Charlieface Would you please see my edit and let me know whether this affects your thoughts on Capabilities and UserAssessments.
    – BVernon
    Oct 7, 2021 at 17:56
  • @Charlieface I understand your point that I haven't named UserSkillResponses well. However I don't understand the difference between what you're saying the table should consist of and what it currently is. To be clear, it should have one response per UserAssessment (not Assessment) and Skill combination. So UserAssessmentId and SkillId make up the primary key, and ResponseId is just an identity column. That said, would you mind writing out how it would look different if I take your suggestion? Other than maybe dropping the ResponseId identity column, does it not look how you described?
    – BVernon
    Oct 7, 2021 at 18:13
  • @Charlieface Thank you so much for taking the time to help me!
    – BVernon
    Oct 7, 2021 at 18:14

1 Answer 1

1

Seems you need one of the new features in EF Core 5... Many-to-Many. You can find that feature description here https://learn.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key#many-to-many

Also here is a good breakdown of the code for the feature and how to implement it in your code. https://www.entityframeworktutorial.net/efcore/configure-many-to-many-relationship-in-ef-core.aspx

I don't believe this will autogenerate if you scaffold your DBContext and Entity classes so you will have to modify both the DBContext and Entity classes for the UserSkillResponses table and associations back to the Users and Skills tables.

1
  • Hi Chris, thanks for your suggestion. However I don't have a problem working with many to many relationships; but it's not the problem here. The problem is having foreign keys to multiple tables which are "above" rather than "below", so to speak. Think of a hierarchy where a child has 2 parents instead of 1. If you take the child and its parents in isolation you can flip it around as though the parents were children and work naturally in EF. However, if you need to put other tables in the mix then you are no longer able to do that. If you feel I'm missing something here please do elaborate.
    – BVernon
    Oct 8, 2021 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.