Re: Unary many-to-many relationships
Posted by: Rick James
Date: March 03, 2012 09:31AM

Sorry, I work better from seeing one or more proposals, complete with CREATE TABLEs, their relationships, and some numbers about how common items are.

A many-to-many relationship is usually embodied in a table something like
CREATE TABLE many2many (
   foo_id INT UNSIGNED ...,
   bar_id INT UNSIGNED ...,
   (optionally, some info on the relationship)
   PRIMARY KEY(foo_id, bar_id),
   INDEX(bar_id, foo_id)
) ENGINE=InnoDB;
Where foo_id and bar_id are the ids into two (or one?) table.

A "hierarchy" is often done this way, and involves "self joins":
CREATE TABLE stuff (
   id INT UNSIGNED ...,
   parent_id INT UNSIGNED ..., -- matches some id in same table (perhaps NULL for the 'root')
   ... other fields ...
   PRIMARY KEY(id),
   INDEX(parent_id)  -- often handy, eg for finding all children
) ENGINE=InnoDB;
That is a DAG (Directed Acyclic Graph). If there are loops in the graph, you probably need many2many, instead.

Options: ReplyQuote


Subject
Written By
Posted
Re: Unary many-to-many relationships
March 03, 2012 09:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.