3

I'm modelling an ontology in SQL using a tree to indicate relations between concepts. My problem is how to disambiguate concepts in a graph/tree without replicating concepts.

Eg. I have the concept of courses: "karate", "sailing" (lessons one can take from instructors) and places: "gym", "boat club" where these courses can be taken. In the graph it will be:

gym        boat club
   has     has
     courses
   of        of
karate    sailing

How can I model this graph to avoid boat club having karate courses and without duplicating the concept of courses?

Thanks!

Daniele
  • 359
  • 7
  • 15
  • Consider some of the following questions and see my edited answer below: http://stackoverflow.com/questions/5237301/return-all-nodes-in-many-to-many-hierarchal-tree http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree#192462 look for *closure table* and *adjacency list* – Matthew Mar 15 '11 at 15:27

2 Answers2

2

I would turn the whole data architecture around and think about this as a graph. Have a table for all the concepts (nodes) - gym, boat club, courses, karate, sailing - and another table to create the links (vertices) between them. That table looks something like:

Links
  from (concept foreign key)
  to (concept foreign key)
  link_type (has/of/etc)

It may take a lot more SQL to build up all your information in memory, but this schema will handle these relationships simply.

dunedain289
  • 2,348
  • 18
  • 21
  • Thanks, this is what I'm trying to do! (http://stackoverflow.com/questions/5083210/modelling-trees-or-graphs-sql-how-to-separate-properties-of-nodes-and-nodes-info). The problem is I don't know how can I state "boat club" has only "courses" of "sailing" (and not of karate) given that "courses" is mapped to "sailing" and "karate" children. – Daniele Feb 22 '11 at 20:05
  • If all of your links are 3-element (X has Y of Z), you could add another key to the Links table. If links are n-element, I'm not exactly sure how to represent them. – dunedain289 Feb 22 '11 at 22:45
  • eventually I decided to implement a disjoint table, it sounds one possible way to solve the problem.. – Daniele Feb 24 '11 at 09:02
0

The table that contains the data Karate and Sailing would contain the primary keys for the other two tables as foreign keys. This would allow for you to have as many of each data type as you like while still being able to traverse the tables to get your information.

PCasagrande
  • 5,302
  • 3
  • 27
  • 36