Schema – Track statuses in database

database-designschema

I am a bit struggling with this one:

Table name = "Issue" with Columns

Id
Name
OpenedBy
OpenedAt
ClosedBy
ClosedAt
PaidBy
PaidAt
RefundedBy
RefundedAt
AssignedTo
AssignedAt
UnassignedBy
UnassignedAt

I want to track history of changes on that table.
I naturally feel that this solution it's not good.
Therefore, I am asking.

I looking for solution where I can capture this in a proper way but without writing very complex and time consuming queries against it.

Especially when I use entity framework.

Thanks so much to everyone.

Best Answer

I don't think that your schema is a bad way to track when issues are opened, closed, paid, etc.

It wont track when people update or delete rows. To track these you could use a trigger. If you're on SQL Server Change Data Capture (CDC) is another option but it can be a little inflexible to work with.