0

I have a child relationship table as

CREATE TABLE ItemRelationships
(
Item1 int(11) unsigned NOT NULL,
Item2 int(11) unsigned NOT NULL,
INDEX(Item2),
FOREIGN KEY(Item1) REFERENCES Items(ItemID) ON DELETE CASCADE,
FOREIGN KEY(Item2) REFERENCES Items(ItemID) ON DELETE CASCADE,
PRIMARY KEY(Item1,Item2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

for a list of items:

CREATE TABLE Items
(
ItemID int(11) unsigned NOT NULL AUTO_INCREMENT,
Title varchar(255),
ParentID int(11) unsigned,
INDEX(ParentID),
PRIMARY KEY(ItemID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

If there is a relationship between Item1 and Item2, they both belong to the same parent.

But I do not have the Parent yet. How can I create unique ParentIDs from the relationship table as

CREATE TABLE Parents
(
ParentID int(11) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ParentID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

And then update ParentID in the table Items.

5
  • We might be able to help you better if you provided the specific nouns rather than generic ones like "parent" and "item". I don't have a picture of what the ultimate structure should look, and who. As stated, it feels "overconstrained".
    – Rick James
    Jan 7 at 17:30
  • @RickJames I couldn't find an analogy. They are indeed random items. Something like a list of products. If two are similar, they belong to a category. The problem is that in a list of products, we have named categories (e.g., books), but here, the parent/category is unnamed (just a group of similar items).
    – Googlebot
    Jan 8 at 6:27
  • What if item1 and item2 are related, plus item2 and item3 are related -- does this mean that all 3 have the same "parent"?
    – Rick James
    Jan 8 at 18:18
  • @RickJames sure all three belongs to the same parent. See it this way, the relationship table shows if two items are identical. If item2 is item1 and item3 is item2, then item1 and item3 are evidently identical.
    – Googlebot
    Jan 9 at 2:22
  • If i1=i2 and i3=i4 and i2=i3, deriving the single common parent is tricky. You might set p1 for i1 and i2, then p2 for i3 and i4, then find there is a problem when you get to the last item matchup.
    – Rick James
    Jan 9 at 6:10
0
  • ItemRelationships is a "many-to-many" relationship.

  • ChildMap is not indexed correctly. It probably needs:

    PRIMARY KEY(ItemID),
    INDEX(ParentID, ItemID)
    
  • Do you already have the Parent for the pair of children? That is, are you looking for an UPDATE or for an INSERT?

  • If you get ParentID set, do you still need ItemRelationships? (It is easy to get "all children".)

1
  • you're absolutely right. The index of ChildMap is incorrect. In fact, ChildMap is redundant because it is a one-to-many relationship, and I can replace it with Items table. I updated the question to clarify the issue.
    – Googlebot
    Jan 8 at 6:24

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.