Jump to content
Sign in to follow this  
milefaker

row journaling

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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,

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

"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.

Share this post


Link to post
Share on other sites

"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.

Share this post


Link to post
Share on other sites

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

Headwords -< Definitions -< Versions

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.