The following exchanges between Lee Irons and Tom Holden shed some light on the differences between RootsMagic and two other leading genealogy software programs (Legacy Family Tree 7.5 and Family Tree Maker 2012) and what can give rise to incompatabilities when data is transferred between them via GEDCOM.

 
Hi Tom,
There are software comparison reviews out there, but they are pretty much at the
thousand-foot altitude, basically useless to advanced users. I've been considering
putting together a comparison that gets down into the weeds on these three programs.

My email below just scratches the surface, as you know. The industry definitely
needs daylight shed on it so that people can see what they are really getting when
they purchase one of these programs. It might help provide some motivation to the
developers to get moving on cutting-edge enhancements and stop nibbling at the crumbs.
If you would like to post my email below as a precursor to a greater effort, then
please feel free. ;-)
Lee

-- Tom Holden wrote:
 
> Hi Lee,
>
> Great explanation of the differences among the three programs. I'm not aware
> of anybody having done this publicly. Would you be willing to contribute
> something like this to the SQLite Tools For RootsMagic wiki, or let me quote
> this on it? All in the interest of advancing our understanding of the
> characteristics of data imported into RM from other programs ;-)
>
> Tom
>
> ---Original Message---
> From: Lee Irons
> Sent: Sunday, December 18, 2011 11:53 PM
> To: 'Tom Holden'
> Subject: RE: Completed conversion of shared family facts
> ...
> Yes, you are correct regarding the differences in approach between the three
> programs. LFT 7.5 does 1-or-more-citations : 1-event/fact, but has a
> quasi-1-citation : many-events/facts functionality by allowing a source
> citation to be copied and pasted to other events and then appear to be one
> citation for the many events in reports and publications. LFT does not have
> "shared facts," but when a fact type is applied to a marriage, it becomes a
> marriage fact. FTM 2012 has a 1-or-more citations : 1-or-more-events/facts.
> In practice, the user can create 1 citation and link it to many events/facts
> with ease. It differentiates between individual facts, which can be shared
> by anyone, and marriage facts, which can only be applied to married couples.
> RM5, of course, takes the approach of having shared events. I'm sure the
> database structure is quite different for all of these.
>
> I would say that, of the three, LFT 7.5 has advanced merge, find,
> search-and-replace, and report/publication functionality. FTM 2012 has
> advanced sourcing, mapping, and heads-up-display functionality. RM 5 has
> advanced place management functionality (with county checker and place
> details), and has the only roles-for-shared-facts functionality of the
> three. All of these other functionality in all three has strengths and
> weaknesses.
>
> Regarding the way I cite sources and build evidence, my migration to RM 5
> taught me a lot of lessons and showed me some weaknesses in what I was
> doing. I have spent the last couple of weeks improving my methodology and
> now have something that would work with LFT or FTM without me needing to use
> the shared facts capability of RM 5. The Roots Magic developers seem to
> have their hands full with playing catch-up to the advanced capabilities of
> FTM and LFT, so I am going to keep my eye on RM for a while. Family Tree
> Maker 2012 is not perfect. It only recognizes current-day places (does not
> validate the existence of counties based upon date). Its fact sentence
> functionality is weak. It also does not chronologically order the facts on
> the person screen, nor does it allow the user to manually reorder them, so
> it looks messy. Also, its report and publications capability needs some
> work. However, LFT 7.5 has weak advanced sourcing , which makes me have to
> do a lot of clicking around, and copying and pasting to accomplish what I
> want. So I am focused on trying to make FTM 2012 work for me right now,
> because its source citation capability is that good. I have sent some
> enhancement requests off to Family Tree Maker developers for advanced fact
> management (needs better fact sentence creation capability and the ability
> to reorder facts), advanced place management (needs an Original Place Name
> field as opposed to the current Place field which only recognizes
> current-day place names for valid name checking and automatic mapping and a
> Date-Line Checker, similar to RM's County Checker), and advanced report
> customization. Legacy Family Tree would have to build in some Advanced
> Sourcing capability to bring me back. I've put in the enhancement requests
> to the LFT developers, but it looks unlikely that they are going to happen.
>
> Cheer!
>
> Lee
>
> ---Original Message---
> From: Tom Holden
> Sent: Friday, December 16, 2011 8:26 AM
> To: Lee Irons
> Subject: Re: Completed conversion of shared family facts
>
...
> I'm interested in your comment on FTM2012 as I was seriously thinking of
> giving it a try just about when RM5 came out and distracted me. Also, your
> description that it "merges the duplicate source citations into single
> source citations with multiple facts of multiple individuals linked to them"
>
> sounds different from what you were trying to do in RM5 which was to merge
> matching independent facts into shares of a single fact. Maybe the goal is
> the same (single citation for all) but the tools and solutions are
> necessarily different. In RM, every citation is uniquely linked to one
> person, or one family or one event - a 1:1 relationship. I wonder if, in FTM
> and LFT, multiple persons|families|events can link to one citation (many:1).
>
> Two very different database designs which could account for the difficulties
> in migrating between LFT and RM whereas it might be naturally easier between
> FTM and LFT. I did not even look at the LFT GEDCOM to see how it might
> differ.
>
> Tom
>
>
>
> ---Original Message---
> From: Lee Irons
> Sent: Friday, December 16, 2011 1:48 AM
> To: 'Tom Holden'
> Subject: RE: Completed conversion of shared family facts
>
> Tom,
>
...
> On the other hand, I have found that Family Tree Maker 2012 imports my
> Legacy file and merges the duplicate source citations into single source
> citations with multiple facts of multiple individuals linked to them. It
> does exactly what I want. It also has the ability to place a pin anywhere
> on a Bing map location to record the geocode for a place that it can't
> automatically find. The reporting capability isn't all that great, but I
> can move the data back and forth through a GEDCOM 5.5 file between Family
> Tree Maker and Legacy and use Legacy's reporting capability, which is top of
> the line.
>
> Long story short, I think I have found my solution using other software.
> Roots Magic had some nice things (like Place Details), but it appears that
> it would be too difficult to convert my data.
>
> Feel free to keep playing around with my data file as you like. I'll still
> keep an eye on the forum and get updates and upgrades to Roots Magic. Kind
> of a hobby.
>
> Cheers,
>
> Lee

The database documentation was done largely on RootsMagic version 5.0.0.3 and those released in the several days following. It’s unlikely that the database schema has changed as of the most-recent version. However, any changes may be detected by opening a database created by each version with a SQLite manager, then exporting either a query of the full sqlite_master table or the database schema (just the SQL field of that table wrapped in a transaction) and comparing the exports from each version using a text editor that has a compare function (e.g. Notepad++).

The page RM5 Database System Catalog displays the full sqlite_master table from 5.0.0.6.

For future comparisons, below is the schema from 5.0.0.6:

BEGIN TRANSACTION;
CREATE TABLE AddressLinkTable (LinkID INTEGER PRIMARY KEY, OwnerType INTEGER, AddressID INTEGER, OwnerID INTEGER, AddressNum INTEGER, Details TEXT );
CREATE TABLE AddressTable (AddressID INTEGER PRIMARY KEY, AddressType INTEGER, Name TEXT COLLATE RMNOCASE, Street1 TEXT, Street2 TEXT, City TEXT, State TEXT, Zip TEXT, Country TEXT, Phone1 TEXT, Phone2 TEXT, Fax TEXT, Email TEXT, URL TEXT, Latitude INTEGER, Longitude INTEGER, Note BLOB );
CREATE TABLE ChildTable (RecID INTEGER PRIMARY KEY, ChildID INTEGER, FamilyID INTEGER, RelFather INTEGER, RelMother INTEGER, ChildOrder INTEGER, IsPrivate INTEGER, ProofFather INTEGER, ProofMother INTEGER, Note BLOB );
CREATE TABLE CitationTable (CitationID INTEGER PRIMARY KEY, OwnerType INTEGER, SourceID INTEGER, OwnerID INTEGER, Quality TEXT, IsPrivate INTEGER, Comments BLOB, ActualText BLOB, RefNumber TEXT, Flags INTEGER, FIELDS BLOB );
CREATE TABLE ConfigTable (RecID INTEGER PRIMARY KEY, RecType INTEGER, Title TEXT, DataRec BLOB );
CREATE TABLE EventTable (EventID INTEGER PRIMARY KEY, EventType INTEGER, OwnerType INTEGER, OwnerID INTEGER, FamilyID INTEGER, PlaceID INTEGER, SiteID INTEGER, DATE TEXT, SortDate INTEGER, IsPrimary INTEGER, IsPrivate INTEGER, Proof INTEGER, STATUS INTEGER, EditDate FLOAT, Sentence BLOB, Details BLOB, Note BLOB );
CREATE TABLE ExclusionTable (RecID INTEGER PRIMARY KEY, ExclusionType INTEGER, ID1 INTEGER, ID2 INTEGER );
CREATE TABLE FactTypeTable (FactTypeID INTEGER PRIMARY KEY, OwnerType INTEGER, Name TEXT COLLATE RMNOCASE, Abbrev TEXT, GedcomTag TEXT, UseValue INTEGER, UseDate INTEGER, UsePlace INTEGER, Sentence BLOB, Flags INTEGER );
CREATE TABLE FamilyTable (FamilyID INTEGER PRIMARY KEY, FatherID INTEGER, MotherID INTEGER, ChildID INTEGER, HusbOrder INTEGER, WifeOrder INTEGER, IsPrivate INTEGER, Proof INTEGER, SpouseLabel INTEGER, FatherLabel INTEGER, MotherLabel INTEGER, Note BLOB );
CREATE TABLE GroupTable (RecID INTEGER PRIMARY KEY, GroupID INTEGER, StartID INTEGER, EndID INTEGER );
CREATE TABLE LabelTable (LabelID INTEGER PRIMARY KEY, LabelType INTEGER, LabelValue INTEGER, LabelName TEXT COLLATE RMNOCASE, Description TEXT );
CREATE TABLE LinkTable (LinkID INTEGER PRIMARY KEY, extSystem INTEGER, LinkType INTEGER, rmID INTEGER, extID TEXT, Modified INTEGER, extVersion TEXT, extDate FLOAT, STATUS INTEGER, Note BLOB );
CREATE TABLE MediaLinkTable (LinkID INTEGER PRIMARY KEY, MediaID INTEGER, OwnerType INTEGER, OwnerID INTEGER, IsPrimary INTEGER, Include1 INTEGER, Include2 INTEGER, Include3 INTEGER, Include4 INTEGER, SortOrder INTEGER, RectLeft INTEGER, RectTop INTEGER, RectRight INTEGER, RectBottom INTEGER, Note TEXT, Caption TEXT COLLATE RMNOCASE, RefNumber TEXT COLLATE RMNOCASE, DATE TEXT, SortDate INTEGER, Description BLOB );
CREATE TABLE MultimediaTable (MediaID INTEGER PRIMARY KEY, MediaType INTEGER, MediaPath TEXT, MediaFile TEXT COLLATE RMNOCASE, URL TEXT, Thumbnail BLOB , Caption TEXT COLLATE RMNOCASE, RefNumber TEXT COLLATE RMNOCASE, DATE TEXT, SortDate INTEGER, Description BLOB);
CREATE TABLE NameTable (NameID INTEGER PRIMARY KEY, OwnerID INTEGER, Surname TEXT COLLATE RMNOCASE, Given TEXT COLLATE RMNOCASE, Prefix TEXT COLLATE RMNOCASE, Suffix TEXT COLLATE RMNOCASE, Nickname TEXT COLLATE RMNOCASE, NameType INTEGER, DATE TEXT, SortDate INTEGER, IsPrimary INTEGER, IsPrivate INTEGER, Proof INTEGER, EditDate FLOAT, Sentence BLOB, Note BLOB, BirthYear INTEGER, DeathYear INTEGER );
CREATE TABLE PersonTable (PersonID INTEGER PRIMARY KEY, UniqueID TEXT, Sex INTEGER, EditDate FLOAT, ParentID INTEGER, SpouseID INTEGER, Color INTEGER, Relate1 INTEGER, Relate2 INTEGER, Flags INTEGER, Living INTEGER, IsPrivate INTEGER, Proof INTEGER, Bookmark INTEGER, Note BLOB );
CREATE TABLE PlaceTable (PlaceID INTEGER PRIMARY KEY, PlaceType INTEGER, Name TEXT COLLATE RMNOCASE, Abbrev TEXT, Normalized TEXT, Latitude INTEGER, Longitude INTEGER, LatLongExact INTEGER, MasterID INTEGER, Note BLOB );
CREATE TABLE ResearchItemTable (ItemID INTEGER PRIMARY KEY, LogID INTEGER, DATE TEXT, SortDate INTEGER, RefNumber TEXT, Repository TEXT, Goal TEXT, SOURCE TEXT, RESULT TEXT );
CREATE TABLE RoleTable (RoleID INTEGER PRIMARY KEY, RoleName TEXT COLLATE RMNOCASE, EventType INTEGER, RoleType INTEGER, Sentence TEXT );
CREATE TABLE SourceTable (SourceID INTEGER PRIMARY KEY, Name TEXT COLLATE RMNOCASE, RefNumber TEXT, ActualText TEXT, Comments TEXT, IsPrivate INTEGER, TemplateID INTEGER, FIELDS BLOB );
CREATE TABLE SourceTemplateTable (TemplateID INTEGER PRIMARY KEY, Name TEXT COLLATE RMNOCASE, Description TEXT, Favorite INTEGER, Category TEXT, Footnote TEXT, ShortFootnote TEXT, Bibliography TEXT, FieldDefs BLOB );
CREATE TABLE WitnessTable (WitnessID INTEGER PRIMARY KEY, EventID INTEGER, PersonID INTEGER, WitnessOrder INTEGER, ROLE INTEGER, Sentence TEXT, Note BLOB, Given TEXT COLLATE RMNOCASE, Surname TEXT COLLATE RMNOCASE, Prefix TEXT COLLATE RMNOCASE, Suffix TEXT COLLATE RMNOCASE );
CREATE INDEX idxAddressName ON AddressTable (Name);
CREATE INDEX idxChildFamilyID ON ChildTable (FamilyID);
CREATE INDEX idxChildID ON ChildTable (ChildID);
CREATE INDEX idxChildOrder ON ChildTable (ChildOrder);
CREATE INDEX idxCitationOwnerID ON CitationTable (OwnerID);
CREATE INDEX idxCitationSourceID ON CitationTable (SourceID);
CREATE UNIQUE INDEX idxExclusionIndex ON ExclusionTable (ExclusionType, ID1, ID2);
CREATE INDEX idxFactTypeAbbrev ON FactTypeTable (Abbrev);
CREATE INDEX idxFactTypeGedcomTag ON FactTypeTable (GedcomTag);
CREATE INDEX idxFactTypeName ON FactTypeTable (Name);
CREATE INDEX idxFamilyFatherID ON FamilyTable (FatherID);
CREATE INDEX idxFamilyMotherID ON FamilyTable (MotherID);
CREATE INDEX idxGiven ON NameTable (Given);
CREATE INDEX idxLabelType ON LabelTable (LabelType);
CREATE INDEX idxLinkExtId ON LinkTable (extID);
CREATE INDEX idxLinkRmId ON LinkTable (rmID);
CREATE INDEX idxMediaCaption ON MediaLinkTable (Caption);
CREATE INDEX idxMediaFile ON MultimediaTable (MediaFile);
CREATE INDEX idxMediaOwnerID ON MediaLinkTable (OwnerID);
CREATE INDEX idxMediaURL ON MultimediaTable (URL);
CREATE INDEX idxNameOwnerID ON NameTable (OwnerID);
CREATE INDEX idxNamePrimary ON NameTable (IsPrimary);
CREATE INDEX idxOwnerDate ON EventTable (OwnerID,SortDate);
CREATE INDEX idxOwnerEvent ON EventTable (OwnerID,EventType);
CREATE INDEX idxPlaceAbbrev ON PlaceTable (Abbrev);
CREATE INDEX idxPlaceName ON PlaceTable (Name);
CREATE INDEX idxRecType ON ConfigTable (RecType);
CREATE INDEX idxResearchItemLogID ON ResearchItemTable (LogID);
CREATE INDEX idxResearchName ON ResearchTable (Name);
CREATE INDEX idxResearchOwnerID ON ResearchTable (OwnerID);
CREATE INDEX idxRoleEventType ON RoleTable (EventType);
CREATE INDEX idxSourceName ON SourceTable (Name);
CREATE INDEX idxSourceTemplateName ON SourceTemplateTable (Name);
CREATE INDEX idxSurname ON NameTable (Surname);
CREATE INDEX idxSurnameGiven ON NameTable (Surname, Given, BirthYear, DeathYear);
CREATE INDEX idxWitnessEventID ON WitnessTable (EventID);
CREATE INDEX idxWitnessPersonID ON WitnessTable (PersonID);
COMMIT TRANSACTION;