modelling ontology graph in SQL: the two fathers problem

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!开发者_StackOverflow社区

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.

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.