Problem

I was going through my database looking for United States of America and USA in the Place, Place Details and Description fields on the Edit Person screen. I am using the following query for the Place field:

-- Place with less than greater than.sql
SELECT e.eventid,
e.placeid, --e.siteid, e.familyid,
e.ownertype, -- 0 = person; 1 = family
e.ownerid,
e.ownerid,
n.surname,
n.given,
f.name,
p.name AS Place
 
FROM eventtable e
LEFT JOIN nametable n
ON e.ownerid = n.ownerid
 
LEFT JOIN facttypetable f
ON e.EventType = f.FactTypeID
 
LEFT JOIN placetable p
ON e.placeID = p.PlaceID
 
WHERE p.placetype = 0 -- 0 = Place, from Edit Person screen for a fact
--and e.eventtype = 29 -- Residence
--and E.ownerid = 1576
--and substr(p.name, 1,1) = "<"
--and (place like "%USA%"
--or place like "%of America%")
ORDER BY surname, given ;

These are some of the records that were in the results:
EventID PlaceID OwnerType OwnerID Surname Given Name Place
9505 1680 1 1290 Marriage Washington, Franklin, Missouri, USA
13606 3213 1 1756 Marriage Silver Lake, Kenosha, Wisconsin, USA
14302 1662 1 1802 Marriage Union, Franklin, Missouri, USA
18211 1680 1 2199 Marriage Washington, Franklin, Missouri, USA
20784 4416 1 2405 Marriage Rockingham, Virginia, USA
20976 4490 1 2428 Marriage , Rice, Minnesota, USA
23733 5314 1 2459 Marriage Luebbering, Franklin, Missouri, USA
27138 6062 1 2406 Marriage of Virginia, USA
27781 5055 1 2682 Marriage Madison, Kentucky, USA
28555 6351 1 2441 Marriage of Bound Brook, Somerset, NJ, USA
12116 2694 1 1576 Allen Linda Lee Marriage ,, Indiana, USA
16001 3593 0 6087 Briggs Augustus Horace Birth , Fountain, Indiana, USA
19479 4178 0 7055 Briggs Nellie May Burial Lonedell, Franklin, Missouri, United States of America
17510 3872 0 6529 Briggs Sally Wood Birth , Scioto, Ohio, USA

The records that are Owner Type 1 are no longer in the nametable. Which is why there is no surname or given name. Except for the Bold one. How did things get this way? I am not sure. The only thing I delete in the eventtable is the blank death dates using SQLite Expert Personal. Otherwise I use RM to delete stuff. Could this be happening when I have SQLite open and the database open and I get the database lock message when trying to delete an event or someone in RM?

I am not quite sure what VACUUM does. If it gets rid of orphaned records, could it include these kind of records? I know RM has an Clean Phantom Records button but it obviously is not removing these records. Should I report this to RM or can you do that since you work with them closer than I do? Or I think you do anyway.

I have done the reindex, clean phantom records and compact the database many times in RM. I even do the reindex and VACUUM in SQLite.

The Linda Lee Allen one above displays St. Mary’s Lutheran Church, Kenosha, Wisconsin, United States in the Place field on the Edit Person screen. When I go to the PlaceTable and find St. Mary’s Lutheran Church, Kenosha, Wisconsin, United States. It has a PlaceID of 7002. When I go to the EventTable and find PlaceID 7002, it has a OwnerID of 497 and EventType of 300. When I go to the NameTable and find OwnerID 497, it displays Marcotte Roberta. When I go to RM and find Marcotte Roberta, it displays Aurora, Adams, Colorado, United States in the Place field. I am soooo confused. I obviously missed a boat somewhere but where?

Answer

Yes, you missed the boat. In the EventTable, the OwnerID when the OwnerType=1 points to the FamilyID in FamilyTable. From there, FatherID is the PersonID of the Husband and MotherID is the PersonID of the Wife. It’s not that the records of OwnerType 1 are no longer in the NameTable; they are not intended to point directly to one person in the NameTable but indirectly to two people. The Marriage event is a couple or family event. That the bolded one in your example points to a person is merely coincidental – the OwnerID is a FamilyID that happens to correspond to an OwnerID in the NameTable but you should not be linking the two directly when it is EventTable.OwnerType 1, only when 0. There are 1 or 2 other OwnerType values for events in the EventTable. You will find them in the Database Design Spreadsheets. You don’t want to delete them.

SQLite VACUUM is renamed Compact in the RM UI. Records that have been deleted leave space in the database file that is unavailable to the Operating System for other files. VACCUM sucks it out, so to speak, and returns it as unused space to the OS.

The RM Clean Phantom Records is relatively comprehensive but not as aggressive as my Delete Phantoms.

And, no, I have no special connection to RM Inc.; just another lowly user.

Tom