How to store footnotes or comments per field
The tables I've designed are usually straightforward, something like this:
CREATE TABLE `account_trans` (
`at_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`at_recorded` date DEFAULT NULL,
`at_description` varchar(65) DEFAULT NULL,
`at_amount` decimal(22,4) DEFAULT NULL,
`at_price` decimal(22,4) DEFAULT NULL,
PRIMARY KEY (`at_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
For a new project, I need a provision to store footnotes or comments on all of these fields; for instance, comments as to how the price was calculated. (The comments will be stored in a separate table.) I was wondering how others might have solved a similar pattern. I had two ideas, neither of which is all that attractive to me:
1) Add a footnote ID field for each field in the primary table. The problem with this solution is that most of the time, most of the footnote ID fields will be blank or zero, as footnotes will probably be added to only one or two items per record.
2) Change the primary table to something like a "name-value" table, that is, each field in the example above would become a separate record in the table tied together by a single transaction ID, something like this:
CREATE TABLE `account_trans` (
`at_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`at_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `trans_items` (
`ti_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ti_at_id` int DEFAULT '0',
`ti_comment_id` int DEFAULT '0',
`ti_trans_type` varchar(10) DEFAULT null,/*This is the 'name' field*/
`ti_trans_value` decimal(22,4) DEFAULT null,/*This is the 'value' field*/
PRIMARY KEY (`ti_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
This solution has a couple of problems, it's less human-readable, requires more effort when summing transactions, and doesn't account for adding footnotes to date and other non-numeric fields. It might have other efficiency problems, too.
If this problem makes sense, I'd appreciate any feedback and better solutions.
Thanks.