Article:		Using Topic Maps to Extend Relational Databases
Subject:		queries using associations
Date:		2003-03-27 15:05:47
From:		Taylor Cowan
This is interesting, however, I see a weakness even in simple queries such as finding out "which employees are taking the XML class". You can get the topic id's associated with the XML topic, however, Employee_12 doesn't help you get back to a real employee record...unless you split the topic id into table/id then query again. The only way I can see to do this would be for the topic map id to be identical to the employee id, or for the employee table to have a column called "topic_map_id". Was that the ideal solution, for each table to have a column to associate a row back to its topic? I couldn't review the supplied DB. Perhaps next time you can supply a DDL or something for those of us who don't use *.mdb databases. Taylor

Previous Message

Next Message

• queries using associations
  2003-04-01 04:12:34 Marc de Graauw [Reply]
  
  Taylor wrote: You can get the topic id's associated with the XML topic, however, Employee_12 doesn't help you get back to a real employee record...unless you split the topic id into table/id then query again.

  
  MdG: Yes, you would either have to do that using SQL's right/left/length functions or one could make a two-part key in the topic table, one part with the related table, i.e. 'employee' or 'department', and one part with the employee id or department id. Things get trickier when the tables in the databases themselves have multi-column primary keys. The best solution there I think would be to carefully select a few tables which supply topics and support the full primary keys of those tables in the topic table. Of course the article was meant as a guideline, in concrete applications plenty of design issues remain!

  
  Taylor: The only way I can see to do this would be for the topic map id to be identical to the employee id, or for the employee table to have a column called "topic_map_id". Was that the ideal solution, for each table to have a column to associate a row back to its topic?

  
  MdG: No, I would always prefer to leave the original tables as they are and use the solution sketched above.

  
  Taylor: I couldn't review the supplied DB. Perhaps next time you can supply a DDL or something for those of us who don't use *.mdb databases.

  
  MdG: Sorry, I was considering using the free MySQL but guessed most readers do not have that installed either. I wasn't too happy about using a propietary db format eiher, but it seemed the best solution for a sample db in this case. Next time I will provide a complete DDL.

  
  Cheers, Marc