Sfrgttk

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) 

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

• A photo has one photo_owner


• property and unit are both photo_owners


• When creating a property or unit, a record in photo_owner must first be created in order to use for the entity's photo_owner_id.

This design allows you to specify exactly which entities can own a photo, and which ones cannot. Do you want a "tree" entity to be able to own a photo? Add a photo_owner_id column to the table. It also avoids having an "item" table, which is too nebulous of an entity definition in my opinion. Additionaly, if there are properties that the owner of a photo must have, you can add them in one place (the photo_owner table) instead of having to add them to multiple tables.

Abandon FOREIGN KEYs; they do not work for all situations. Do the cascading delete in the client -- or perhaps in a stored procedure.

Your parent and child have identical structure; that is usually a no-no in schema design. Your item has essentially nothing in it; that seems strange. So... Have only one table. It has an id and a parent_id, which is enough to implement a 1:many (1 parent to many children) relationship.

Bottom Line:

1. Think through whether you have a 1:many, or many:many relationship.


2. Build the minimum number of tables required for that. (1 or 3). Do not think about FKs.


3. Sketch out the SELECTs you will need.


4. Design the minimum number of indexes needed to make the queries efficient. (Keep in mind that 'composite' indexes are often useful.)


5. 
   Now see if FKs can help you. Add the ones that work; write code where they don't.


6. Further refine the queries.