CODEWITHSUNDEEP
sqlsql-serveref-core-2.0
Ali Kleit
Ali Kleit

Reputation: 3649

Storing a foreign key for the main primary key in each table

Say we have the table Users with an ID as a primary key, and the user has the following related entities:

UserSubjects(SubjectId <PK>,UserId <FK>)
SubjectPapers(PaperId<PK>,SubjectId<FK>)

Now as per the above tables, for security reasons the userId must be passed as a parameter to get all the SubjectPapers for a Subject that are related to the user instead of just passing the SubjectId.

take that method for example:

 public List<SubjectPaper> GetBySubject(int userId, int subjectId)
 {
     return _context.SubjectPapers
         .Include(k => k.UserSubject)
         .Where(k => k.SubjectId == subjectId && k.UserSubject.UserId == userId)
         .ToList();
 }

In the above method, the user has to be included in the query to validate that he is making the request to get one of his subjects.

What if also SubjectPapers has more related entities theoretically. Will storing a UserId Foreign key improve the query's performance by adding a non-clustered index on both columns (UserId and SubjectId)? Is there any side effects? or is there any other way to avoid including the parent user each related entity? Any suggestion is appreciated.

Upvotes: 0

Views: 68

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89246

If UserId is requried to find a Subject, then Subject is a "Weak Entity" and the UserID should be the leading column in it's compound primary key.

UserSubjects(UserId <PK,FK>,SubjectId <PK>)

IE like

public class User
{
    public int UserId { get; set; }
    public virtual ICollection<Subject> Subjects { set; } = new HashSet<Subject>();
}
public class Subject
{
    public int UserID { get; set; }
    public int SubjectId { get; set; }
    public virtual User UserId { get; set; }
}

and configured like this:

modelBuilder.Entity<Subject>().HasKey(s => new { s.UserID, s.SubjectId });
modelBuilder.Entity<Subject>().Property(s => s.SubjectId).ValueGeneratedOnAdd();

This optimizes for retrieval by UserID and prevents the Subject table from requiring multiple indexes. It has a single clustered index on (UserID,SubjectID) instead of needing two separate indexes on the two columns.

Upvotes: 1

Related Questions