row journaling

[milefaker]

Can anyone suggest a way to implement row journaling? I.e., keeping past versions of a given record accessible but never displaying them, unless for some reason I want to go back and examine long-discarded changes. I can already do that by consulting backed up versions of the whole database, but it's hard to know which backed-up version to look in for something I want to find.

This database is being used to compile dictionary data. There are two main tables:

• "headwords": contains individual words that have unique spelling, together with information about their frequency of occurrence, sources that contain them, and graphic variants.
• "definitions": contains definitions, parts of speech, notes on usage, and so forth.

Records in "headwords" are related one-to-many to records in "definitions", because a single distinctive word form may have unrelated meanings and parts of speech. So for example, "can" appears only once in "headwords", but several times in "definitions", each time with all the things that make the various words spelled "can" different from each other.

I use the serial number from "headwords" to look up word forms and populate the appropriate field in "definitions". Content is added to "headwords" occasionally, but there is very rapid and repeated alteration of the material in "definitions", and I would like to be able to review past changes of that material at will.

In "headwords", each headword field has unique contents. But in "definitions", no single field has unique contents except the serial number.

Thanks for any suggestions or references.

[bcooney]

Can more than one headword be related to a definition?

I don't understand what you're doing when you say that you "lookup word forms and populate the appropriate field in definitions."

First thoughts are to have an active and inactive flag in the definition record. Any edits set the orig def rec to inactive, and duplicate the original def record for editing. Then you could see inactive definitions. But, if def recs are used by many headwords, then you'd need a join table.

Search audit trails and visit WorldSync's site for a rollback solution, FMDataGuard,

[milefaker]

Thanks for your quick reply.

Can more than one headword be related to a definition?

No. Headword to definition is one to many.

I don't understand what you're doing when you say that you "lookup word forms and populate the appropriate field in definitions."

I mean that "definitions" contains a field whose contents are populated from "headwords". That way, there is no danger that I will accidentally alter that content manually when working in definitions, and if I need associated material from headwords, it can also be used to populate a field in definitions.

First thoughts are to have an active and inactive flag in the definition record. Any edits set the orig def rec to inactive, and duplicate the original def record for editing. Then you could see inactive definitions.

How would I know that any two records represented different versions of the same material?

Search audit trails and visit WorldSync's site for a rollback solution, FMDataGuard,

Thanks for the reference.

[bcooney]

"How would I know that any two records represented different versions of the same material?"

Well, on a Headwords record, you'd see a portal to ACTIVE definitions. You could also see a portal to INACTIVE definitions.

Each definition record could contain a ParentID (the ID of the definition record from which it was copied). Then you could follow the audit trail of def rec changes.

[milefaker]

"How would I know that any two records represented different versions of the same material?"

Well, on a Headwords record, you'd see a portal to ACTIVE definitions. You could also see a portal to INACTIVE definitions.

I see what you mean. However, a single headword is not a single word, merely a single form. To continue with the example of "can", once I create a record in the Definitions table for "can" in the sense "able" and another record for "can" in the sense "metal container", I want them to remain separate, and I want to see only the history of each separate "thread" of development. Otherwise, things could get extremely confusing.

[comment]

It sounds like you could use three tables instead of just two:

Headwords -< Definitions -< Versions