SQL Tree or Graph Modeling: How to Separate Node and Node Information Properties

Question

I model a conceptual ontology as a polytree .

Using the edge list model , this will be:

CREATE TABLE nodes(
  nodeID CHAR(1) PRIMARY KEY
);

CREATE TABLE edges(
  childID CHAR(1) NOT NULL,
  parentID CHAR(1) NOT NULL,
  PRIMARY KEY(childID,parentID)
);

My problem is how can I model it in SQL so that the nodes can have other properties like "types". For instance.

(father-node) **Music**  

It has

(child-node) **jazz** [type: genre], **soul** [type: genre]
(child-node) **concert** [type: performed], **DJ set** [type: performed]

Answer 1

You should check out Celcos nested recruitment model, http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

But in any case, just add the genre identifier to the node table

CREATE TABLE nodes(
  nodeID CHAR(1) PRIMARY KEY
  genre_id
);