Designing a table with foreign keys to multiple "parent" tables

Question

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);

Answer 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.