Tracking every property change

Question

I am a computer science student from Germany and I work at a small IT startup. I have been assigned a project with challenging requirements. The customer wants a human resources management tool that includes a work planner, holiday planner, payroll administration, and more. The challenge is that the customer wants to track every property change throughout the entire system, and each property needs a "validFrom" property. Additionally, some properties require a "validUntil" property as well.

The ultimate goal is to be able to view the system state for a given point in time and also to be able to supplement changes or add future changes. This means that we need to implement a system that keeps track of every property change and allows us to access the system state at any given point in time.

The project is a winforms application with c# and a sqlite database with optional entity framework layer.

A basic example: We have users in the system:

class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Password { get; set; }
    public ICollection<Permission> Permissions { get; set; }
}

To be able to track changes for "Name", "Password", and "Permissions", we have a separate database table for each of them:

class UserNameRecord
{
    public int Id { get; set; }
    public int UserId { get; set; } 
    public string Name { get; set; }
    public DateTime ValidFrom { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime DeletedAt { get; set; }
}

and for collection something like:

class UserPermissionsRecord
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public CollectionAction Action { get; set; } // added or removed
    public Permission Permission { get; set; }
    public DateTime ValidFrom { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime DeletedAt { get; set; }
}

The problem is that we have over 200 tables, and the number keeps growing. Fetching data for the views is quite complicated and takes a noticeable amount of time. Additionally, validating constraints is a nightmare.

I want to address this problem because it is causing significant slowdowns for me, and I need some advice on how to do it better.

Answer 1

you need to ask the question, if you need to be able to do all of this on a database level.

if you follow the approach of normalizing all your data you will end up with a convoluted mess of tables.

if you don't normalize some things, you will end up with way less tables but gain redundancy in data. Of course that's not what you desire but it's usually something you can live with...

so in terms of your user object there you can just persist the whole entity (with name and password in one table) and look at it as a snapshot at a certain point in time. as for audit trail purposes add a column per such entity recording what was changed compared to the previous version of that record, in some serialized format. that way you can pull the entire change history rather quickly

in terms of the project management triangle this one is cheap and fast... but it lacks the capabilities of precisely changing one property without touching the rest, which will come as a shortfall once you have a history and want to insert another change into an existing history

in terms of the project management triangle, the previous suggestion sacrificed "good"... if we want to keep that, we either have to sacrifice "fast" or "cheap"

if you can not live with such an approach, another suggestion is to generate the entity classes based on the higher level objects by marking their properties that need to be maintained in separate tables.

TLDR --> use automatic code generation, for example T4

put your business objects into a separeate assembly.

create some attributes to mark properties of those (attribute classes in the same assembly)

have a look at the properties, you will notice that you can extract a couple of things all of those extra property tables have in common. not all of them will look the same, but you can group them into distinct (or not so distinct) groups sharing the same "treatment" ... for a lack of a better word

(if you end up with not so distinct groups, like multiple treatments per property are possible then it's not about which treatment a property gets, but which combination)

the attributes you need to create in this case need to take on all the information for such a property to put it into a treatment class (has ValidFrom, CreatedAt, DeletedAt; has validUntil; etc)

the reason behind putting all of that in a sepparate assembly is that you can independently build that assembly and use it to analyze those classes at build time of your main project.

Create T4 Templates as needed to generate the entity classes automatically at build time, based on the attributes

this way you can implement each "treatment" class (how certain properties are treated in terms of storage)

finding and implementing each "treatment" and generalizing those is key here

once you have the entities, generating create table statements with T4 is the same with different syntax.

also recombining those entities to the business objects or splitting business objects into those Entites is a generalizable process, again T4 can help you to create domain specific code to do that in the same way.

if you go down this road, you solve things on a "treatment" class level and once you need a new property or business object class, the corresponding entities and tables are generated by T4, once you wrote the business object class and throw the attributes in. So this one solves the problem in the medium or long term by abstracting the treatment of properties

the analysis of the relationships and the corresponding property "treatments" is the big unkown here. This can be a hand full and you are done... or you get special case after special case...

Answer 2

Maybe 'Temporal Tables' could be a solution