Quote from Tom Holden on 2026-01-14, 2:45 pm

You will find a few prior or relevant discussions among the results of this search of this forum. It's not a new phenomenon. In one, I hypothesized it might be the result of a defective or crashed RM Split Place operation, akin to what happens when there's a crash during Add Person.

Quote from Tom Holden on 2026-01-14, 3:01 pm

Another prior reference is Place Details without a Place from 12 or so years ago. At that time it was possible to add a Place Detail through the user interface with the Place left blank.

Quote from kevync on 2026-01-14, 3:36 pm

I spot checked databases prior to RM11 -- that said its seems to have happened in RM 11 -- this made me go hmm -- maybe related to change in journal modes?
I suspect it might be possible that it related to merging duplicate place with the RM UI.

 

Quote from thejerrybryan on 2026-01-14, 4:27 pm

I haven't used PlaceDetails for years. I rewrote your diagnostic query slightly a follows.

WITH BadMasterID AS
(
Select PlaceID, Name, MasterID, Count(PlaceID) AS CC
FROM PlaceTable AS P1
WHERE PlaceType = 0 and MasterID <> 0
GROUP By MasterID
)

SELECT A.PlaceID AS PlaceID_A, A.MasterID AS MasterID_A, A.Name AS Name_A, A.CC AS A_CC, B.PlaceID, B.Name
FROM BadMasterID AS A
LEFT JOIN PlaceTable AS B ON A.MasterID = B.PlaceID

The problem was not there in my RM7, RM8, or RM9 database. It was there in my RM10 database. And it's still there in RM11.

I do get a bit of junk. I couldn't get the image to go into the message, so I did an upload. It looks like there are a few MasterID's that need to be zeroed out.

 

 

Quote from kevync on 2026-01-14, 8:17 pm

I always get a little confused when to use LEFT vs INNER join -- if I were to do this as an UPDATE query -- wouldn't I want to use INNER JOIN (on last line)?

WITH BadMasterID AS
(
Select PlaceID, Name, MasterID, Count(PlaceID) AS CC
FROM PlaceTable AS P1
WHERE PlaceType = 0 and MasterID <> 0
GROUP By MasterID
)

SELECT A.PlaceID AS PlaceID_A, A.MasterID AS MasterID_A, A.Name AS Name_A, A.CC AS A_CC, B.PlaceID, B.Name
FROM BadMasterID AS A
Inner JOIN PlaceTable AS B ON A.MasterID = B.PlaceID

Quote from thejerrybryan on 2026-01-14, 10:08 pm

I don't think INNER JOIN vs. LEFT JOIN is the issue in this case. I think you just want the following.

UPDATE PlaceTable
SET MasterID = 0
WHERE MasterID != 0 AND PlaceType = 0;

It would work ok without the MasterID != 0 clause, but without including it will update all the MasterID  fields for all the Places in your database, including the ones that are already 0. And you won't get a "nothing updated" message when it's done.

The idea is that when  PlaceType = 2, you have a Place Detail and the MasterID for a Place Detail is to be joined with a PlaceID for a Place where PlaceType = 0.  But when PlaceType = 0 so that you have a Place, the MasterID is meaningless. It doesn't join to anything, no matter if MasterID is 0 or not 0. I do agree that MasterID really should be 0 in this situation, but the data is never used. Data that is never used doesn't actually have to be zeroed out, even if it's better if it is.

I did the LEFT JOIN just to investigate the situation. I was curious which of the meaningless MasterID values for Places might match to other places and which might match to nothing.

Quote from kevync on 2026-01-15, 10:05 am

FWIW this is what AI said

The "Mutually Exclusive Fields" School of Thought

Your observation that MasterID has a value when PlaceType is 0 (even though the documentation says it should be 0) points to a conflict between Schema Rules and Data Evolution.

1. Contextual Nullability (The Current Issue)

In your table, the MasterID field acts as a pointer:

• If PlaceType = 2: MasterID is required (it links the Detail to the Parent Place).

• If PlaceType = 0 or 1: MasterID is "out of scope."

The school of thought here suggests that when PlaceType is 0 or 1, the MasterID must be zeroed out (or set to 0) to prevent "Ghost Relationships." If a value exists there, a query joining PlaceID to MasterID might return a false association, leading the software to think a top-level Place is actually a sub-detail of another random place.

---

2. The "Overloaded Table" School of Thought

This design is often called a "One True Lookup Table." Instead of having two tables (one for Places and one for PlaceDetails), the architects put everything in one.

• The Risk: Data "bleeding." If you convert a PlaceType 2 record into a PlaceType 0 record but forget to zero out the MasterID, you create Orphaned Metadata.

• The Best Practice: High-integrity database schools advocate for Check Constraints. In a perfect world, the database would have a rule:

  CHECK (PlaceType IN (0,1) AND MasterID = 0) OR (PlaceType = 2 AND MasterID > 0)

---

3. Why is MasterID populated when it shouldn't be?

If you are seeing non-zero values in MasterID for PlaceType = 0, you are likely looking at one of three things:

1. Legacy Artifacts: The record used to be a Place Detail (Type 2) and was changed to a Place (Type 0), but the cleanup script didn't "zero out" the MasterID.

2. Pointer Reuse: The software might be using that field for an undocumented purpose (like merging or temporary IDs) when the type is 0.

3. Data Corruption: A bug in the application layer is inserting values into the wrong column index.

---

Summary for your Specific Table

In this specific genealogy schema, the school of thought is Explicit State Management. Since PlaceID and MasterID are both integers, a non-zero MasterID in a PlaceType 0 record is "Dirty Data."

It should be zeroed out because:

• It violates the technical documentation (Field 271).

• It creates a risk of recursive loops (a place appearing to be its own parent).

• It misleads any third-party reporting tools using the SQL directly.