1

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) 
  • 1
    What do parent and child represent? Are these genuine parents (as in mother_of)? Is the relationship recursive, i.e. can a child also be a parent? – Lennart Jun 24 '18 at 10:08
0

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

  • Hi there, so yes there are data differences between child and parent, so they can not be in the same table, a child can not have a child of his own, and a photo can not belong to a child AND a parent, it's one or the other. Thanks! – bstenm Nov 19 '16 at 13:40
  • Sure, I've updated my question with the two tables. Thanks!! – bstenm Nov 19 '16 at 14:23
  • Is the fact that a photo can belong to only parent OR child a 'HARD' requrement? As it is now, i can see some problems with AUTO INCREMENT PK in parent/child referencing the same item – Vladislav Zalesak Nov 19 '16 at 15:26
  • yes sorry there should not be auto increment on parent and child. forgot to remove that. could u elaborate a little on the 2 mapping tables? Cheers!! – bstenm Nov 19 '16 at 16:12
  • Have a table mapping photo (or item) to parent (two columns, item_id and parent_id) and another table mapping item to child(two columns, item_id and parent_id) In adition, Ok, i see that property means like house and unit is maybe room? if i am correct then the way i would do this is i would add a new unitTypeId of type 'overview' being an abstract unit and i would assign photos only to unit and photos of property would be assigned to this abstract unit – Vladislav Zalesak Nov 19 '16 at 16:59
0

enter image description here

  • 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.

-1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.