This seems like a very common requirement but wondering on the best way of implementing it. I have a number of entities in my system e.g. employer / supplier / buyer / seller e.t.c. and each entity needs a common history table with things like

DocumentId ( links to the entity ) DocumentType HistoryText HistoryDateTime

Should I use a single table to refer to all entities or create one per entity? If I have one per entity I can enforce referential integrity well however it seems quite wasteful to have many tables just for history when this is so common across each table

How would I model this table in LLBLGEN, presumably a history collection property would not be automatically created because no foreign keys can exist, should you create a property manually and pass in the appropriate document type and identifier when building the collection to return?

Thoughts please

• create a new catalog, for your history data
• tables you want to archive are present in this catalog as well, though have all a version ID in the PK, or you change PK's altogether (as db state versioning is really something else, so FK's are gone) and have an identity PK now.
• when you update or delete a row, a trigger kicks in and adds a history row in the history catalog.

this makes sure you have history data if you want to (per table, not per db state! db state versioning is very very difficult, as you have to have a central version id and you get a lot of lock nightmares) and also that your working set is kept small and only contains the data you work with: all history data is elsewhere.

Thanks Frans,

This is good advice if I was seeking to log old and new changes for entities however I didn't mean this. I meant history in terms of user / system comments made against an entity e.g. a typical table would store the user id and user comment.