How to store footnotes or comments per field
Posted by: Brent Bigler
Date: February 12, 2013 12:37PM

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.

Options: ReplyQuote


Subject
Written By
Posted
How to store footnotes or comments per field
February 12, 2013 12:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.