Advanced C# - Common SQL Table Structures


Problem Statement

Our goal is to build a simple inventory system. However, we want a generic inventory system, one that allows for items to be categorized and custom attributes to be added. The categories themselves can have categories and attributes. This provides a means to add some hierarchy to the customer implementation and utilize categories as “tags” in some scenarios.


We will create only two models: Category and Product (however many more can be created). We want to utilize the same underlying repository class and re-use as much SQL and code as possible. To make the interaction with SQL a bit easier, we will utilize the Dapper micro-ORM for its CRUD capabilities and syntactical sugar in executing SQL.


Model Design

First thing’s first. Let’s design our data model in C#.


Base Instance

We want to have a common subset of information for every instance. A common model will allow us to create a general table structure and build an efficient system. Let’s use the following class for all entities in our system as a base.

public abstract record class Instance
{  
    [Key]
    public int InstanceId { get; set; }
    
    public string Name { get; set; }
    public string Description { get; set; } ="";
    public bool SystemOwned { get; set; } =false;  
    
    [Computed]
    public CategoryCollection Categories { get; set; } = new CategoryCollection();  
    
    [Computed]
    public DateTime CreatedTimestamp { get; set; }  
    
    [Computed]
    public CustomAttributes CustomAttributes { get; set; } = new CustomAttributes();
}

This model is fairly simple:

  • InstanceId: Identifier of the entity

  • Name: A unique name for the entity

  • Description: An optional description of the entity

  • SystemOwned: A flag indicating whether this is owned by our code or the user has defined this instance (useful for building modules on top of our structure)

  • Categories: The associated categories for the instance

  • CreatedTimestamp: When the entity was first created