document my database

April 27, 2008 6:58 PM

I have a particular propriety database application that has a great feature for users to document notes and changes to the db. Annotated screenshot here. Basically, you click on the little yellow sticky note and the documentation window pops up and users can enter notes about changes, etc. Its so useful I would like to use it all the time.

I am now creating a database project that I would like to recreate this documentation feature. I figured out the database structure for this by some creative sleuthing, but I am having trouble how how to link the documentation record to the particular field (and record) that it is documenting. For instance if I have a field in the table, Field1, and I have a button to pop up the documentation window next to that field, how do I automatically insert data into a 'reference field' into the documentation table that says this comes from Field1? I guess I can change the value of some variable and then automatically fill the field when data is entered into the documentation form.

Problem is, I am having trouble doing this in MS Access. Any ideas?

posted by buttercup to computers & internet (3 comments total)

You know the column name to which you're attaching the note from the GUI, so the easiest way to approach it would have the table structure for the documentation be something like:

id (unique identifier for the note, autoincrement, PK)
parentid (unique identifier of the main record, FK)
dt (timestamp of when the note was added, now() function could autofill it)
originatingColumn (column name)
content (text storage for the actual note)
author

Can only speak in generalities, without seeing the ERD of your database of course. I've make the assumption you're only documenting one table. I suppose you could add an originatingTable column to extend that over a whole application.

So your query to view the information for a particular column of a particular record would be something like:

SELECT * FROM documentation
WHERE parentid = [[Current Record]]
AND originatingColumn = [[Current Column]]
ORDER BY dt DESC;

Where [[ ]] are variables you would have to grab values for before running the query.
posted by Static Vagabond at 8:05 PM on April 27, 2008

Vagabond has it, but at the heart of the OP's Q there are some concurrency issues, aren't there? For instance, if this is a new record, as in the example screenshot, the original Data table entry form hasn't been submitted, and so has no associated id PK for use as the FK index in the Documentation table. So use flow is user hits "new record" button, starts entering data, hits "new documentation" button, submits, more "new documentation" and submit, then finally submits data. Trouble is, the PK for the Data table isn't generated until the last step, but we need it to enter into the FK field in the Documentation table submits in the earlier steps. There are many solutions to this, as it's not an uncommon problem. One is run an INSERT query as soon as the user hits "new data record" with a bunch of NULL for values. Then you can pull the PK of this new dummy record for adding to the Documentation table. This is a pretty bad solution. Another way would be to simply cache all your INSERT calls until the user submits to the Data table. Then the Data table INSERT gets run first, we pull the PK from that and add it to the Documentation table INSERT statements, and submit them.

Another solution would be to use something other than an AUTOINCREMENT PK for the Data table. A concatenation of the datetime and session name, for instance (if you only do the dt and two users create new records at precisely the same microsecond, you've got a problem). Something, anyway, that a) is mathematically guaranteed never to collide and b) is procedurally (or at least predictably) generated (mainly to help assure ourselves that (a) is always true). So when the user hits "new record" we generate our PK, which we can then use in the FK record for the Documentation table, submit all our INSERT's for that, and then use the same value as the PK in our Data table INSERT.
posted by ChasFile at 9:33 PM on April 27, 2008

If all your tables have an arbitrary primary key called id:

Table called notes.

fields:
notable_type -- indicating which table the note is on.
notable_id -- the value of the id column of the row in the table
notable_field -- the column of the table on which you're commenting.
noted_by -- the user who is making the note
note -- the contents of the note
created_at -- a timestamp that defaults to NOW()

So if you want to make a note about: table foo, row id 42, column 'bar', you'd insert:
'foo', 42, 'bar', buttercup.id, 'this is my comment'

if all tables have a serial pkey named id then this will span the entire app pretty cleanly. just need to throw a few things in transactions and call it a day. If they don't, you'd probably want to revert to a per-table solution. It'd be possible to span the app using more complex structures, but that gets messier and messier.

I know this is roughly the same solution as what static vagabond proposed, but I missed his 'originatingTable' mention, and then didn't feel like deleting what i wrote since i thought my terms were pretty clear.

FWIW, I'm in the midst of writing a CRM for my new venture and I did exactly this, then created some libs that allow me to simply mark my models and columns as 'notable' or not. I allow NULL on the column field to allow notes on the entire row rather than a specific column.

I'm sure a lot of database people would cringe hard at the improperly normalized setup, but as long as everything is transactionfied reasonably, it works quite well.
posted by Project F at 11:24 PM on April 27, 2008