There are 117. Here's how I found them, for future reference:
SELECT a.`ttl_title_id`, b.`ttl_title_id`, a.`ttl_title_code` FROM `titles` a, `titles` b WHERE a.`ttl_title_code` = b.`ttl_title_code` AND a.`ttl_title_id` <> b.`ttl_title_id` AND a.`ttl_title_id` < b.`ttl_title_id` ORDER BY a.`ttl_title_id` LIMIT 0, 1000
Negative duration values, in the case of 11 records in the VW_trans_composite_eth_prop view, have revealed some data entry errors in the "preceding titles" and "related titles (newer)" fields. This suggests that there may be other such errors which are not apparent, because their dates happen to be in the correct sequence. Therefore we propose creating an automated check on the data, to locate such inconsistencies. This is an outline of how the check would be done:
However, I see a problem with this. If a title is created as a result of a merger of two or more preceding titles, each of which has a different date, it will be impossible to tell whether there is an error or not. Ditto in the case of subsequent titles, in the case of a subdivision. Waiting for word from JS-R on how to handle this.
I have now carried out the insertion, and constructed a detailed audit trail to pass on to JS-R and VG. Here's the opening section of the audit document (the rest is very long, and consists of precise records of every operation):
____AUDIT TRAIL FOR THE AUTOMATED INSERTION OF SPREADSHEET DATA____ Use this document to check the validity of inserted records and trace the source of any errors that may be found. WHAT HAS BEEN DONE: - A new title record has been inserted for each record in the spreadsheet. - The title has been linked to its property. All properties referred to in the spreadsheet appear to have already existed in the database. so no new property records were created. - All owners recorded in the spreadsheet have either been: - discovered as already existing in the database, or - inserted as new records into the database and each owner has been linked to their title(s). - A new mortgage record has been inserted for each mortgage in the spreadsheet. - All lenders recorded in the spreadsheet have either been: - discovered as already existing in the database, or - inserted as new records into the database and each lender has been linked to their mortgage(s). - A simple algorithm has been used to determine whether owners and lenders are individual or institutional, and their records have been configured accordingly. I have strong confidence in this algorithm, but there may be occasional errors. - All "other documents" recorded in the spreadsheet have either been: - discovered as already existing in the database, or - inserted as new records into the database and each document has been linked to its title(s). WHAT HAS NOT BEEN DONE: - No attempt has been made to set values for the ethnicity of lenders or owners. This cannot be done in an automated fashion. - No attempt has been made to discover seller information for the new records. This is also best done by humans, but it's possible we might be able to automate it if time permits. BACKUPS I have backups of the database taken immediately before and immediately after the process. AUTO_INCREMENT VALUES PRIOR TO INSERTION OF NEW RECORDS FROM SPREADSHEET These values represent the FIRST id and LAST id of a record inserted as part of this process. Within this range, data in the table has been inserted by the automated process. TABLE FIRST ID LAST ID titles 2300 2738 owners 1863 2292 lenders 553 691 mortgages 929 1306 other_docs 388 518 owners_to_titles 9678 10363 mortgages_to_titles 3246 3623 docs_to_titles 1086 1328 lenders_to_mortgages 1130 1564 No new records were inserted in the props table; presumably all the new titles relate to properties already in the database. FULL INSERTION RECORD This is the complete record of record creation through the process. Use this to figure out exactly what happened in the case of any given record. Operation # 1 Processing record with id # d1e1150 Found 1 property records. Using property id #109 Inserted title record # 2300 Processing owners Inserted owner id # 1863 Inserted owner-to-title link id # 9678 Inserted mortgage id # 929 Inserted mortgage-to-title link id # 3246 Processing lenders for this mortgage. Inserted lender id # 553 Inserted lender_to_mortgage link id # 1130 Processing other documents. Inserted other_doc id # 388 Inserted doc-to-title link id # 1086 Finished processing record d1e1150. [...]
A database project to collect historical data on properties and titles.
|<< <||Current||> >>|