Next to the actual data I have in a table, I need to save some data about those fields. I'm hesitant to call it metadata, because while it is exactly that, metadata has a specific meaning within databases sometimes, and this is not exactly what I mean.
Example: I have a table, and for each field I want to know some sort of state. Lets say 'unchecked', 'ok', 'faulty' etc. I could just double the columns, add a new 'status-field' per column, but that feels wrong. A different example would be to safe how the data currently came to be, for instance calculated, automatically retrieved of manually entered.
Some extra info: I might want to save all changed data later: a sort of history table. While this might be a completely different problem, I suppose that it might actually help in this case: if I for instance have a key-value storage for the history, I could maybe add the metadata there?
Is there any default solution to add this sort of data to a database? The final goal is to add these fields to an existing model, one that contains several tables/relations. I'm not even sure if a one-solution-per-table, or a solution-for-all is possible?
In the simple case of "ok/not ok" statusses, the logical query belonging to this would be "get me all records that are ok (meaning: with all rows begin ok). With all the statusses on a distinct location one could do a count on those. When adding an extra field-per-column, I can't seem to think of quicker query then just adding a whole bunch of
WHERE x_status=ok AND y_status=ok AND... etc. The difference in information-type is lost, so there is a hidden property that I feel might need it's own... place. The fact that it is data-about-data shouldn't be lost, but I'm not sure how exactly.