Mysql – How to design parent, child, photos relationships in thesql when photos can belong to either parent or child

database-designMySQL

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) 

Best Answer

Question to answer:

  • Are there data differences between child and parent?
  • Can a child have child of its own?
  • Why not store child and parent in the same table?
  • Can a photo belong to a child AND parent?

Added based on answer

  • Currently, a photo CAN belong to child a parent
  • Given the structure know i MAY look into 'Extension' table... Have one for parents, referenced by photo and have a Extension ie. parent_child_ext table containing data for child, reference to parent etc and have photo reference only the parent page then. This solution is based on whole table design and info about it :-/.

  • Is there any way you could post full info about parent/child columns?

ANSWER: Depending on business requirements, i would probably go with two mapping tables to join photo and parent/child