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.
Best Answer
To me, this feels right ;-) In order to prevent typos, I think that this could (and should) be implemented as a foreign key to another table with just the possible states.
If, necessary, I'd again use foreign keys to other tables for this. These tables would cover the available methods or instruments.
Update 1 Quite often, the number of different states (unchecked, faulty, ok, etc.) is rather small. A foreign key of the type TINYINT would probably suffice and minimizes the memory overhead.