Let's say I have a parent table and a child table with parent_is as foreign key. Now I want to store photos that could either belong to parent or child. so I could have a parent_id and a child_id foreign keys in the photos table: when the photo belongs to parent then the child_id foreign key column would be null and vice-versa. But instead of that I decided to go for a different design: I now have a item table and I get the parent table primary key (parent_id) to also be foreign key referencing the item_id column in the item table, and the child table primary key (child_id) also as a foreign key to the same item_id column in the item table. So now instead of having two different foreign keys ( referencing to child_id and parent_id) in my photos table I only have one (referencing to item_id):
CREATE TABLE `parent` (
`parent_id` int(10) unsigned NOT NULL,
`name` varchar(40) NOT NULL,
PRIMARY KEY (`parent_id`),
CONSTRAINT `parent_ibfk_1` FOREIGN KEY (`parent_id`)
REFERENCES `item` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE)
CREATE TABLE `child` (
`child_id` int(10) unsigned NOT NULL,
`name` varchar(40) NOT NULL,
PRIMARY KEY (`child_id`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`child_id`)
REFERENCES `item` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE)
CREATE TABLE `item` (
`item_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`item_id`))
CREATE TABLE `photo` (
`photo_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`item_id` int(10)unsigned NOT NULL,
`name` varchar(40) NOT NULL,
PRIMARY KEY (`photo_id`),
CONSTRAINT `photo_ibfk_1` FOREIGN KEY (`item_id`)
REFERENCES `item` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE)
I hope that's clear so far! So that works but now my problem is if I want to delete a parent record: I would delete the corresponding item_id record in the item table which would automatically delete the corresponding record in the parent table thanks to the foreign key, which then would automatically delete the records in the child table belonging to that parent. All good except that I would be left with the records in the item table corresponding to the child records that have just been deleted. The item table would not know that some child records have been deleted unless the item_id in the item table was also a foreign key referencing the corresponding child_id in the child table. I've got the feeling my design is wrong? What would be the recommended design in that case?
Here are the real world parent (property) and child (unit) tables:
CREATE TABLE `property` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`companyID` int(11) unsigned NOT NULL,
`serviceTypeID` tinyint(3) unsigned DEFAULT NULL,
`name` varchar(50) NOT NULL,
`address` varchar(100) NOT NULL,
`postCode` varchar(20) NOT NULL,
`city` varchar(50) NOT NULL,
`extraBed` tinyint(3) unsigned DEFAULT NULL,
`checkIn` tinyint(4) unsigned DEFAULT NULL,
`checkOut` tinyint(4) unsigned DEFAULT NULL,
`submissionDate` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `property_ibfk_1` FOREIGN KEY (`id`) REFERENCES `item` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `property_ibfk_2` FOREIGN KEY (`companyID`) REFERENCES `company` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `property_ibfk_3` FOREIGN KEY (`serviceTypeID`) REFERENCES `serviceType` (`id`) ON DELETE SET NULL ON UPDATE CASCADE)
CREATE TABLE `unit` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`propertyID` int(11) unsigned NOT NULL,
`unitTypeID` tinyint(3) unsigned NOT NULL,
`title` varchar(50) NOT NULL,
`smallDescription` varchar(200) DEFAULT NULL,
`largeDescription` varchar(500) DEFAULT NULL,
`submissionDate` bigint(20) unsigned DEFAULT NULL
PRIMARY KEY (`id`),
CONSTRAINT `unit_ibfk_1` FOREIGN KEY (`id`) REFERENCES `item` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `unit_ibfk_2` FOREIGN KEY (`propertyID`) REFERENCES `property` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `unit_ibfk_3` FOREIGN KEY (`unitTypeID`) REFERENCES `unitType` (`id`) ON UPDATE CASCADE)
