Updates mentioned in the preceding post:
ALTER TABLE `props` ADD `prp_street_num` varchar(45) collate utf8_unicode_ci default NULL;
ALTER TABLE `props` ADD `prp_street` varchar(45) collate utf8_unicode_ci default NULL;
In class PropProperty:
$this->addField(new MdhStrField('prp_street_num', 'Street number', '', array(), 45, true));
$this->addField(new MdhStrField('prp_street', 'Street name', '', array(), 45, true));
New table for ethnicities for lenders:
--
-- Table structure for table `lenders_to_ethnicities`
--
CREATE TABLE IF NOT EXISTS `lenders_to_ethnicities` (
`lte_lte_id` int(11) NOT NULL auto_increment,
`lte_lender_id_fk` int(11) NOT NULL,
`lte_ethnicity_id_fk` int(11) NOT NULL,
PRIMARY KEY (`lte_lte_id`),
KEY `lte_ethnicity_id_fk_idx` (`lte_ethnicity_id_fk`),
KEY `lte_lender_id_fk_idx` (`lte_lender_id_fk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
--
-- Constraints for table `lenders_to_ethnicities`
--
ALTER TABLE `lenders_to_ethnicities`
ADD CONSTRAINT `lenders_to_ethnicities_ibfk_1` FOREIGN KEY (`lte_lender_id_fk`) REFERENCES `lenders` (`lnd_lender_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `lenders_to_ethnicities_ibfk_2` FOREIGN KEY (`lte_ethnicity_id_fk`) REFERENCES `ethnicities` (`eth_ethnicity_id`) ON DELETE CASCADE ON UPDATE CASCADE;
In class PropLender:
$this->addField(new MdhOneToManyField('lnd_ethnicities', 'Ethnicities',
'lnd_lender_id',
'ethnicities',
'eth_ethnicity_id',
'eth_name',
'lenders_to_ethnicities',
'lte_lte_id',
'lte_lender_id_fk',
'lte_ethnicity_id_fk',
true,
'eth_name',
true));
Linking titles to preceding titles:
--
-- Table structure for table `titles_to_prectitles`
--
CREATE TABLE IF NOT EXISTS `titles_to_prectitles` (
`ttp_ttp_id` int(11) NOT NULL auto_increment,
`ttp_title_id_fk` int(11) NOT NULL,
`ttp_prectitle_id_fk` int(11) NOT NULL,
PRIMARY KEY (`ttp_ttp_id`),
KEY `ttp_title_id` (`ttp_title_id_fk`),
KEY `ttp_prectitle_id` (`ttp_prectitle_id_fk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3157 ;
--
-- Constraints for table `titles_to_prectitles`
--
ALTER TABLE `titles_to_prectitles`
ADD CONSTRAINT `titles_to_prectitles_ibfk_1` FOREIGN KEY (`ttp_title_id_fk`) REFERENCES `titles` (`ttl_title_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `titles_to_prectitles_ibfk_2` FOREIGN KEY (`ttp_prectitle_id_fk`) REFERENCES `titles` (`ttl_title_id`) ON DELETE CASCADE ON UPDATE CASCADE;
In class PropTitles:
$this->addField(new MdhOneToManyField('ttl_prec_titles', 'Preceding titles',
'ttl_title_id',
'titles',
'ttl_title_id',
'ttl_title_code',
'titles_to_prectitles',
'ttp_ttp_id',
'ttp_title_id_fk',
'ttp_prectitle_id_fk',
true, 'ttl_title_code', true));
My original intention was to populate this last "Preceding titles" field programmatically by parsing the data in the current Traces field, which should also be pointing back at previous title data. However, after looking at some of that data, I think it's going to have to be done by a human. Here's an example of why:
If you go to the Titles table, then choose the third property in the drop-down Property list (005 B:103 L:B), and filter by it, you'll see five results. These should, in theory, represent a succession of titles relating to the same property, by date; the Traces field for each one should point back to the preceding one. However:
- The latest title is 561978L, from 1967. Its Traces field points back to 561975L, but there is no such title in the db.
- The title preceding that by date, 403327L from 1959, points back to two separate titles, 272233L and 256807L. The former does not exist in the database.
- The latter does, though, and this relationship seems logical, pointing back to a 1951 title, which in turn points back to another 1951 entry, which then points back to yet another entry, A.F.B. 26-148-4041E; but this last one has no date. We can (as humans) guess from this that the last one is the earliest, but its absence of date will make coming to that conclusion programmatically a bit of a stretch.
In short, writing code that does a reasonable job of gleaning the preceding title data from what exists in the Traces field is not going to be practical or reliable; a human should go through the data and work this out, I think.