0

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.

6
  • "What I would like to do would be to reuse the CORE Comment data as a class with associated interfaces ...." There are two main paths here. (a) Your code will only be able to rely on the base comment logic and will not account for any inherited details such as the FK: or (b) you will have to write a complex inheritance hierarchy involving generics and inherited overrides. Both work, but they're very different as to behavior and complexity - what do you want?
    – Flater
    Apr 26, 2023 at 4:37
  • @Flater hm, that's an interesting question and I guess I'm not 100% sure. I think (a) is more accurate. My primary goal is to stop having to rewrite things like the CRUD operations and UI components for the comments, and I want to be able to say "Here's an object, add in all the comment input and display widgets to add comments to this object" with the least amount of boilerplate possible. I want to stop building almost identical grids. Although, that said, I do need some level of control over things like which users can access the add comment button. I'm not sure if that really answers you..
    – aterbo
    Apr 26, 2023 at 4:45
  • The next question, based on your comment, is whether you need runtime reusability or if a simple template would suffice. The issue here seems to be the effort of writing it, not necessarily the fact that these comments have different lifecycles once they've been implemented. Correct me if I'm wrong.
    – Flater
    Apr 26, 2023 at 4:52
  • Templating, for sure. No need for runtime reusability. If I could get to a versioned package to share, that would be ideal (I think). I just need to be able to easily maintain a dozen or so applications. These apps were all originally developed as fully-custom programs, but so much of the functionality is similar, I am trying to move to semi-custom for the sake of ease of maintenance (and sanity)
    – aterbo
    Apr 26, 2023 at 4:55
  • I got close to a final answer but then you said "easily maintain". Are you expecting the ability to update your core logic and have this apply to existing instances automatically, or are you happy with the implementation being "locked in" at the time of first implementation?
    – Flater
    Apr 26, 2023 at 4:57

0

Your Answer

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