Database Design Pattern for Adding Notes to Columns

Question

Hi, I'm designing a new DataBase that at the moment has 50 Tables. For many of that tables I have to eventually store a "note" for every column of every tuple. For now I found only 2 solutions:

1. For each column of tables that needs note add a "column note" so for example:

   create table tab (id integer, A varchar(100), B varchar(200))
   

   will become:

   create table tab (id integer, A varchar(100), A_note varchar(300), B varchar(200), B_note varchar(300))
   

2. Add a table Note that store the notes... like that:

   create table notes (id integer, table_name varchar(100), reference_id integer, reference_column varchar(100), note varchar(300)
   

Do you can suggest me other ways? If you are interested I'm using HsqlDB.

Answer 1

Yeah, separate tables are the way to go.

I would do a comment table like cmnt_table or notes_table for each actual table. Have it with the same id as the main table and it will be easy to do a join in queries.

Answer 2

It depends a lot on how sparse your notes are, if they are scattered occasionally across all the tables then a good solution is to have one table containing notes that indexes them by the combination of table and field name.

This moves the addition of notes to the schema out to just one table that needs maintaining without having to touch the originals. You can use a constraint on the table and fieldname columns to restrict which ones can have notes.

I have implemented a variation on that very successfully to add typed tags to a large accounting system.

If a single table is distasteful, consider applying the pattern to clusters of tables.

One big advantage of the single Notes table is that you have one table to search if people are looking by note content. This can be very useful when people put notes in the wrong place.

Extension

I read the requirement as being one Note per Column.

In the case where an optional Note (or Tag) is to be stored occasionally for odd individual row values in columns (ie: the Excel model of attaching comments) then you extend my model above by adding a regular integer key for your rows. That is actually the model I first used. I simplified it for the answer but then realised maybe the requirements were misleading.

Answer 3

You should store the extra data in the same table. This is a one to one relationship and you gain no benefit from splitting them, only complexity.

Answer 4

You don't say what these notes are for. If each note describes the column in general, for example a column named EXPIRY_DATE has a note saying "The date when the ticket expires", there is a built in solution in HSQLDB 2.0.

COMMENT ON MYTABLE.EXPRIRY_DATE IS 'The date when the ticket expires'

And you can retrieve the note for a tablename, columnname pair using a SELECT on one of the INFORMATION_SCHEMA tables.

See the Guide:

http://hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html#N10D75