I think I know what TimF has in mind, a table like the following:
Table
TagId
TagValue
which would grow vertically, and only have the values that have changed. This comes up from time to time, but is not currently possible using only FactorySQL functions (note: in the next major release, block groups will support inserting just the changed rows of a block, which will indeed let you build something like this).
In the mean time, you can do one of a few things. You could just use the groups as they are, and minimize duplicate data using techniques like HandledException suggested. Or, you could get a little creative, and perhaps accomplish this using a block group and a database trigger or a secondary group. I’ll explain…
You could have a block group, with all of the tags in one “value” column. I would also make a copy of that item and change it to look at the “item path” property, with a string data type. I would also have it store row id. The block group would be set to “update the first block” of a “status” table. You would also manually construct a history table with a similar schema to that of status table (“create table block_history like block_status” is a handy query to do that in mysql)
The block group will only write to rows that have changed. Therefore, you could write a database trigger that executes on update, and inserts the new data into the history table.
If you didn’t want to use a db trigger, you could have a standard group in FactorySQL that runs fairly quickly, tracks the last execution time (look into the StoreVariable/GetVariable action item functions), and then runs a query like:
INSERT INTO block_history SELECT * FROM block_status WHERE t_stamp>{lastexec}All in all, a bit more complicated, but if you’re sure you want that structure and you’re up for it, should work out.
Regards,