I recently merged a Sarah with an unknown surname with a Sarah Boardman. I forget which way round I did the merge, but I suspect that I kept the Sarah unknown and then changed her name afterwards. After doing this, Sarah Boardman (1791-1873) appears in the database, but not in the index, as you will see from the following screenshot.
But the problem should not arise in the first place. I did a quick test in a small test database going through the same steps, but this time everying worked as it should.
18604 appears in the Index in both screenshots but without the Birth Year in the first. That’s not an uncommon occurrence having been the subject of complaint for many years. To update the Index requires an edit of the Birth event or the running of the Rebuild Indexes tool which can take a chunk of time for a large database.
This is a well known problem. It’s part of the reason why there is such a strong and recurring recommendation to run the database tools on a regular basis. The tool that most matters is the Re-Indexing tool. Various issues associated with the Birth fact cause the RM table that contains the sidebar Index to include invalid data. These issues include such things as duplicate Birth facts, Birth facts with invalid dates, merging of people which has an impact on Birth facts, etc.
Jerry,
I think the real issue here is Why do the database tools have to be run so frequently. Any database designed with a modern robust database manager should handle index maintenance on its own, without end-user involvement. Of course, poor programming standards and practices can certainly complicate things.
Could you imagine if Fidelity or Schwab had to reindex their systems every time x number of transaction were made? Or if you had to ask your bank to reindex when you wanted to check your balance? Or if you had to ask Amazon to reindex when you wanted to shop for a new USB thumb drive? Or if you had to ask your cable TV company to reindex before you looked at the listings guide?
Maybe the RM programmer should concentrate on enhancing database stability and integrity over adding new functionality.
Table indexes are robustly maintained by the SQLite database engine. It is the Sidebar Index display maintained by the application that is unreliable. That’s because the Birth Year and Death Year values are redundant data fields in the NameTable that are populated by the application from the primary data in the EventTable. For various reasons, as @thejerrybryan describes, the application fails to update those secondary fields. I would add system crashes as another factor.
RM4 did not have the Rebuild Indexes tool, iirc. It was added eventually and conflated, under the one control, the SQLite REINDEX which rebuilds all the database’s table indexes with the repopulating of the Year fields used by the Sidebar Index and other summary outputs.
We all agree that the Birth Year-Death Year display should be more reliable than it is. I wonder if it is even really necessary to have the Year values redundantly stored. Would performance be adversely affected by direct extraction from the primary event dates? If so, then why not use a SQLite Trigger to update the redundant field whenever there is a change to the primary event on which it is based, rather than coding something less reliable in the app?
There are several problems associated with maintaining the birth and death year values for the sidebar Index. I suspect that the developers did not want to have to go through overhead of solving these problems for everybody in the database anytime the sidebar Index is displayed, which is why there is a second copy of the birth year and the death year. The first copy of the birth and death dates is stored with the Birth fact and the Death fact in RM’s EventTable. The second copy of the birth and death years (not full dates) is stored in RM’s NameTable which contains both the Primary name for each person and all the Alternate names. That’s why problems with the birth and death years can show up with the Primary and Alternate names in the sidebar Index.
The main problem for the sidebar Index is that the data in the Birth and Death facts can by “dirty” in various ways. There can be more than one Birth fact and more than one Death fact. Which date is controlling on those cases? The birth date and/or the death date can be invalid. Such invalid dates are called text dates in RM’s user interface. A christening date can be a surrogate for a birth date when a birth date is missing.
In addition, Birth and Death facts can be added. They can be deleted. They can be changed. Any of these activities can change the inferred birth and death years for the person. These are tricky problems but not problems that cannot be solved. RM’s Reindexing tool obviously has the logic to deal with such problems. What I never understood is why that same logic isn’t applied just for one person whenever you exit the Edit Person screen for that person. Tom’s suggestion of a database level trigger would probably be an even better solution.
With some sort of better solution in place, it would be an unusual situation where the sidebar Index needed to be rebuilt. But such a tool should remain in place. However, I think it should be a tool separate from the Reindixing tool which reindexes the database level indexes. These are two totally different indexing processes, and as Tom says the database level indexes are maintained pretty robustly by the SQLite database engine.
Solving this problem shouldn’t be rocket science. Add the same logic to update the birth and year fields that already exist in the indexing tools to the exit process from the Edit Person screen, or to a database trigger. Deconflate the Reindixing tool into one tool for the database level indexes and another tool for the sidebar Index. Done.
