I use Entity Framework Core for persistence. I have several entities (such as Invoice, Quote, Report, etc...) where each entity may contain multiple comments. The Comment entity does not need to know anything about its parent that it is related to.

My problem is that - for each entity that references collection of Comment - Entity Framework Core includes respective foreign key column to Comment table, like this:

 migrationBuilder.CreateTable(
     name: "Comment",
     columns: table => new
     {
         Id = table.Column<int>(type: "int", nullable: false)
             .Annotation("SqlServer:Identity", "1, 1"),
         Text = table.Column<string>(type: "nvarchar(500)", maxLength: 500, nullable: true),
         InvoiceId = table.Column<int>(type: "int", nullable: true),
         QuoteId = table.Column<int>(type: "int", nullable: true),
         ReportId = table.Column<int>(type: "int", nullable: true),
     },
     constraints: table =>
     {
         table.PrimaryKey("PK_Comment", x => x.Id);
         table.ForeignKey(
             name: "FK_Comment_Invoice_InvoiceId",
             column: x => x.InvoiceId,
             principalTable: "Invoice",
             principalColumn: "Id");
         table.ForeignKey(
             name: "FK_Comment_Quote_QuoteId",
             column: x => x.QuoteId,
             principalTable: "Quote",
             principalColumn: "Id");
         table.ForeignKey(
             name: "FK_Comment_Report_ReportId",
             column: x => x.ReportId,
             principalTable: "Report",
             principalColumn: "Id");
     });

Here are the entities:

public class Comment
{
     public int Id { get; set; }
     public string Text { get; set; }
}

public class Invoice
{
     public int Id { get; set; }

     public virtual ICollection<Comment> Comments { get; set; }
}

public class Quote
{
     public int Id { get; set; }

     public virtual ICollection<Comment> Comments { get; set; }
}

public class Report
{
     public int Id { get; set; }

     public virtual ICollection<Comment> Comments { get; set; }
}

Is there a way to safely remove references of foreign keys related to Invoice, Quote, Report from the Comment table as they are redundant from relational logic view point and unnecessarily widen the Comment table.

I use Entity Framework Core for persistence. I have several entities (such as Invoice, Quote, Report, etc...) where each entity may contain multiple comments. The Comment entity does not need to know anything about its parent that it is related to.

My problem is that - for each entity that references collection of Comment - Entity Framework Core includes respective foreign key column to Comment table, like this:

 migrationBuilder.CreateTable(
     name: "Comment",
     columns: table => new
     {
         Id = table.Column<int>(type: "int", nullable: false)
             .Annotation("SqlServer:Identity", "1, 1"),
         Text = table.Column<string>(type: "nvarchar(500)", maxLength: 500, nullable: true),
         InvoiceId = table.Column<int>(type: "int", nullable: true),
         QuoteId = table.Column<int>(type: "int", nullable: true),
         ReportId = table.Column<int>(type: "int", nullable: true),
     },
     constraints: table =>
     {
         table.PrimaryKey("PK_Comment", x => x.Id);
         table.ForeignKey(
             name: "FK_Comment_Invoice_InvoiceId",
             column: x => x.InvoiceId,
             principalTable: "Invoice",
             principalColumn: "Id");
         table.ForeignKey(
             name: "FK_Comment_Quote_QuoteId",
             column: x => x.QuoteId,
             principalTable: "Quote",
             principalColumn: "Id");
         table.ForeignKey(
             name: "FK_Comment_Report_ReportId",
             column: x => x.ReportId,
             principalTable: "Report",
             principalColumn: "Id");
     });

Here are the entities:

public class Comment
{
     public int Id { get; set; }
     public string Text { get; set; }
}

public class Invoice
{
     public int Id { get; set; }

     public virtual ICollection<Comment> Comments { get; set; }
}

public class Quote
{
     public int Id { get; set; }

     public virtual ICollection<Comment> Comments { get; set; }
}

public class Report
{
     public int Id { get; set; }

     public virtual ICollection<Comment> Comments { get; set; }
}

Is there a way to safely remove references of foreign keys related to Invoice, Quote, Report from the Comment table as they are redundant from relational logic view point and unnecessarily widen the Comment table.

• entity-framework-core

1 Answer 1

This is how relational data is referenced. Think of it from the database perspective. If you have an Invoice table and a Comment table where invoices have multiple comments, how do you link them?

If one invoice has one comment, then Invoice could have a CommentId or Comment could have an InvoiceId, or both would use the same PK. (Default for one-to-one relationships)

If invoice can have multiple comments then Comment needs an InvoiceId. (These 3 comments associate with invoice ID #1) Now if comments could be linked to an Invoice and/or a Quote and/or a Report then Comment needs a null-able FK to InvoiceId, QuoteId, and ReportId. The issue here is enforcing whether a comment is actually associated to anything through one of the FK, or associated to multiple things. (InvoiceId set and QuoteId set)

The alternative is to use a many-to-many relationship between the tables and Comments. For instance having an InvoiceComment table, QuoteComment, and ReportComment table that have InvoiceId+CommentId, QuoteId+CommentId, and ReportId+CommentId. This would allow comments to be technically shared between invoice, quote, and reports. If you do not want comments to be shared between tables then it has to be enforced at a code-logic level or you may as well just use separate tables for the comments relating to each parent.

If an invoice can have multiple comments and those comments should be unique, as in not be linked to quotes or reports, then consider something like:

InvoiceComments:
InvoiceCommentId (PK)
InvoiceId (FK)
CommentText

QuoteComments:
QuoteCommentId (PK)
QuoteId (FK)
CommentText

...

The attraction to use a single Comment table is commonly argued to "save space" and avoid duplication, but the only space that is saved is in the schema definition which is a few bytes or kB at most. If you have a million comment rows spread between invoices, quotes, and reports, it is either a million rows in a single table, or a million rows across 3 tables. There is a performance improvement in splitting it across 3 tables as indexing Invoice comments just has to sift through rows relating to invoices, not the additional rows for quotes or reports. You also can enforce data integrity rules and not worry about comments being shared accidentally or orphaned. (accommodating things like cascade deletes) You also have the freedom to expand comments for particular relationships freely, for instance adding a column that is only relevant to Invoices to the InvoiceComment table. In a single table scenario you end up with adding null-able columns then also dealing with soft rules about them being required for Invoice comments and shouldn't be set for other types of comments.