modelling trees or graphs SQL: how to separate properties of nodes and nodes information
I'm modelling a conceptual ontology like a polytree.
Using the edge list model it would be:
CREATE TABLE nodes(
nodeID CHAR(1) PRIMARY KEY
);
CREATE TABLE edges(
childID CHAR(1) NOT NULL,
parentID CH开发者_JS百科AR(1) NOT NULL,
PRIMARY KEY(childID,parentID)
);
My problem is how can I model it in SQL so that nodes can have other properties like "types". Eg.
(father-node) **Music**
has
(child-node) **jazz** [type: genre], **soul** [type: genre]
(child-node) **concert** [type: performed], **DJ set** [type: performed]
You should check out Celkos nested set model, http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
But anyway just add genre id to your nodes table
CREATE TABLE nodes(
nodeID CHAR(1) PRIMARY KEY
genre_id
);
精彩评论