Quote from thejerrybryan on 2024-03-16, 2:03 pm

I would also be willing to help but not lead.

However, I would suggest that as a fundamentally lazy person, I have found the most useful approach to RM8 and RM9 queries is just to use the RM7 DataDefsMstr and to be aware of the most obvious differences.

The most obvious difference is that the RM7 CitationTable has been split into CitationTable and CitationLinkTable in RM8/9. For example, I compare my citations in RM7 to my citations in RM9 by comparing the RM7 CitationTable to a join of the RM9 CitationTable and CitationLinkTable. After I do the join of the two RM9 tables, the columns are essentially all the same as they were for RM7.

Beyond that, some date columns in RM7 have become date/time columns in RM8/9. RM9 has extra columns for the new color sets. Nearly every RM8/9 table now has a new UTCModDate column that wasn't there in RM7. But that's about it for the tables and columns I use.

The change from To Do lists and Research Logs in RM7 to Tasks in RM8/9 has created significant table changes, but I don't use these particular features. I use refreshable groups and saved searches, but I have not had any reason to query any of that data from SQLite.

I'm probably minimizing the differences, but the hard to remember parts are always the columns that need to be joined and the codes for the OwnerType columns (and often you can't do joins correctly without using the OwnerType columns correctly at the same time). Those really have not changed very much other than the ones I have already mentioned. Finally, if I'm just trying to remember the spelling of a column name, I just double click the table name in the SQLite manager and it shows me that table and all its columns.

Quote from Tom Holden on 2024-03-16, 3:22 pm

A consolidated data definitions spreadsheet such as you describe for RM9 would be an addition welcomed by those without the long exposure to prior versions such as Jerry and myself. We have the advantage of deep experience with much of the database structure that is unchanged from or very similar to earlier versions and quick recognition of the exceptions and additions brought about the latest. I confess to forgetting parts and having to cast about on the site to find useful references.

I'm not well qualified as a database designer or administrator or documenter having no training in those roles and am currently occupied by preparing to sell our house and move to another province. Consequently, I'm not in a position to put much effort into such an effort. I can offer to set up a Google Sheets spreadsheet accessible on a page on the website which can be shared with a small group of Editors who could collaborate on it.

A person well qualified in database design and documentation who himself over the last year or so has encountered the maze of fragmented documentation and is doing something about it is @richardotter. Have you looked at his site https://richardotter.github.io/ and near the bottom of the page:

RootsMagic database table listing and notes

One text file per table with notes on how the columns are used.

It would be great if he could be engaged in this undertaking!

Quote from Kevin McLarnon on 2024-03-17, 11:26 am

Thank you both for responding.  Your comments are excellent, as usual.  I’ve checked out Richard’s git site and an update to the RM7/8 data definitions spreadsheet has clear overlaps with his RMTables project.  I’ll reach out @richardotter to see if he would be interested in collaborating on this.  Perhaps, at a minimum, we could share findings and he would be willing to help share expertise with areas that go beyond my skills.

I think Tom’s offer to set up a page on the website with a link to a google doc is the way to go.  I am willing to be a focal point for the effort - monitoring blog page comments and google doc activity, reviewing updates, etc, including trying to recruit some of the folks who have deeper db skills to assist where needed.  Jerry, your offer to help is a major reason for my change of heart and new-found willingness to be the focal point (“lead” is probably too strong of a word for what I can offer.)  That said, my intent is to minimize the impact on you guys, since you both do so much of the heavy lifting already.  I am confident that there are many others like me who have benefitted from your insights and tips.  This is an opportunity for us to pay it forward a bit.  I suspect folks just haven’t seen this thread yet and, once we get more visibility and some traction, there will be others eager to assist and/or lead the update effort.

Tom, I’ll reach out to you directly to sort out the next steps.

Quote from Richard Otter on 2024-03-23, 12:28 am

Hi-

Regarding the initiative to update the various design documents for RM v9....

Thanks, Tom for mentioning my efforts toward that goal.

In the time since, I have put quite a bit of effort into improving what I had;  expanding, reformatting, and making then an Open Source type project.

The tables doc files that I had started have all been moved to their own GitHub repo at-

https://github.com/ricko2001/RootsMagic_Database_Design

I have added a "Contributors" file which lists the authors whose work I have previously relied on. *Please* let me know if I have forgotten someone or some reference. I really don't want any bad feelings.
I have added a "Contributing" file which explains how to contribute to the project.
Anyone is free to use what's in my GitHub repos as indicated by the included "License" file.
And check out the ReadMe file to understand why I want my work on GitHub.

And I have updated my own website to point to the new location.

I am personally not interested in creating a spreadsheet or spending time figuring out how to make an ERD diagraming/analysis tool work with RM's style of polymorphic associations.
I am interested in creating docs that will help my own work and that will help others.
I am open to any suggestions, contributions and ideas.  The file contents still need more work. I have mostly concentrated on the tables that contain genealogical data, not so much on the tables that support RM features. But I'm getting there.

Richard

 

Quote from Kevin McLarnon on 2024-03-23, 1:16 pm

Thanks Richard for agreeing to help with the project effort and for updating the link to your project .

This post was created to gauge interest in a project to update the V7 data definition template spreadsheet to include RM version 9.  Since we’ve now kicked off that project and launched the project webpage and with the corrected link to Richard’s project site, there’s no longer a need for this particular thread to be remain active and we can close it out.

Let’s direct all future comments and inquiries to the RM9 Data Dictionary Project thread.