Where/how do I save extra data per column?

Question

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.

Answer 1

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.

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.

A different example would be to safe how the data currently came to be, for instance calculated, automatically retrieved of manually entered.

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.

Answer 2

The biggest issue I have here is that I really want to be able to separate the meta-data from the normal data. There is something special to the data we're saving in two ways: one is that it is of a different type (metadata) then the other data, the other is that it 'belongs' to another column.

Both of these facts aren't 'visible' in a setup where these points-of-data are just another column.

This isn't an issue per se, but it does feel like the database-equivalent of a code-smell to me. "give me all columns where metadata says x" is going to be harder to query then it should be? At least with more hardcoded column names.

A second part is that in the future we might want to expend on the metadata, so we'd have quality, source, a date maybe.

This all leads to the following proposed sollution:

All this makes me consider a two-step method. In the end, the actual data is saved in a table as-is, for several reasons of wich 'backward compatibility' is a big one. But 'before' that there is a key-value table that saves the data as well. This means a key (probably something limited, not a text-field perse) that correspons with a column, a value that should go in the column, a quality-field, a date and any other things I might need. This will even account for a history of data.

Now this will become really large, so that's one of the reasons that the data in this table will be aggregated towards the initial table every time there is a change.

I'm not really sure yet how feasible this is going to be, but this seems a viable option.