I am working on a small suite of enterprise applications, and I am trying to determine the best way to make them more consistent and maintainable across the board. The applications are .Net Blazer Server apps in C# with SQL Server Dbs using Entity Framework Core, but the question I have is more relevant to db structuring.
I have created numerous component libraries and various interfaces/implementations of different classes and UI elements, but the place I am hung up is how best to structure my SQL tables so that I can reuse the core elements across various custom apps. I always need to customize my classes to account for the SQL Foreign Keys, so I can't package all of my little widgets up in to NuGet packages for easy reuse.
For the focus of this question, I am looking at adding a Comment to various records, such as a Project and an Inspection. Other records I would like to make generic are more complicated, with app-specific metadata, but this Comment class is very simple, say:
public class Comment { public int CommentId { get; set; } public string Comment { get; set; } public DateTime Created { get; set; } public string CreatedBy { get; set; } } Currently, we are using EF Reverse engineer to generate our classes. In each application where I need a comment, I add one table for each Comment implementation, like ProjectComment and InspectionComment in SQL. Each table has the base data above with added foreign key columns as needed to refer to the source, resulting in something like (assuming Inspection is a child reference on Project):
public class ProjectComment { public int ProjectCommentId { get; set; } public string Comment { get; set; } public DateTime Created { get; set; } public string CreatedBy { get; set; } public int Project_Fk { get; set; } } public class InspectionComment { public int InspectionCommentId { get; set; } public string Comment { get; set; } public DateTime Created { get; set; } public string CreatedBy { get; set; } public int Inspection_Fk { get; set; } public int Project_Fk { get; set; } } What I would like to do would be to reuse the CORE Comment data as a class with associated interfaces and prebuild UI components around it and then just drop that in all of my applications as needed.
I have considered things like JSON attributes columns or the EVA pattern, but I have concerns about each (that I won't get into).
My current thought is to have a master Comment table with all of the base Comment entries. And then I will create multiple tables that are one-to-many to reference many Comments to one Project or one Inspection, something like (once translated to C#):
public class Comment { public int CommentId { get; set; } public string Comment { get; set; } public DateTime Created { get; set; } public string CreatedBy { get; set; } } public class InspectionComment { public int Inspection_Fk { get; set; } public int Comment_Fk { get; set; } } public class ProjectComment { public int Project_Fk { get; set; } public int Comment_Fk { get; set; } } public class Inspection { public int InspectionId { get; set; } public virtual List<InspectionComment> InspectionComments { get; set; } // ... Other Properties ... } public class Project { public int ProjectId { get; set; } // ... Other Properties ... public virtual List<ProjectComment> ProjectComments { get; set; } } I'm not exactly sure what my final components will look like, but is this generally how these sorts of reusable data structures would be created? I feel like it should be obvious, but all of the guidance I've found has been related to building reusable and scaleable C# code, but once you're dealing with a database structure (especially if you're reverse engineering), it gets more complicated.
