xml-dev - Topic Maps on SQL

[Charles Reitzel <creitzel@mediaone.net>]

len bullard <cbullard@hiwaay.net> wrote:
>I am reasonably sure that one can implement topic map 
>functionality in a set of relational tables and SQL queries, 
>and then, as with RDF, export a compliant SGML/XML instance 
>of that information.  Do you know of any reason why this 
>would not work?

SQL is ok but not great at associations.  It is terriffic when the
attributes/columns are relatively fixed but falters when the
attribute/column set is variable within the membership of a single
association set.  Put another way, topic maps seem inherently Smalltalk-like
and not very C++-like.

If you are searching a document base, you want to be able to find any
document or element with an attribute "X" containing "Y".  You don't want to
have to find all the tables that contain column "X" and emit the appropriate
SQL for each (possible, but probably inefficient).  More importantly, you
don't want to be either: a) creating new tables all the time or b) adding
new columns to your existing tables.

I have often found it useful to create SQL meta-tables for code lookups,
domain validation, etc.  These work great, but are inefficient for large
data sets.  I have created pure association tables in the past, but have
never been happy with them.  If you were insistent, the tables might look
like this:

CREATE TABLE ASSOC_LINK
(
    ID_A        NUMBER(8)   NOT NULL,   /* SEQUENCE# OF THING A */
    ID_B        NUMBER(8)   NOT NULL,   /* SEQUENCE# OF THING B */
    ASSOC_TYP   VARCHAR(8)  NOT NULL,   /* APP DEPENDENT CODE THAT */
                                        /* WILL TELL YOU WHAT THINGS */
                                        /* A AND B ARE AND WHAT */
                                        /* TABLES THE ACTUAL DATA */
                                        /* ARE IN. MIGHT ALSO */
                                        /* INCLUDE DIRECTION OF */
                                        /* ASSOCATION, ETC. */
    /* YOUR ASSOCATION DATA HERE */
    /* ... */
);

/* META-DATA! */
CREATE TABLE ASSOC_TYPE
(
   ASSOC_TYP    VARCHAR(8)  NOT NULL,   /* PK, LOOK UP FROM ASSOC_LINK */
   DESCRIP      VARCHAR(240) NOT NULL,
   TBL_A        VARCHAR(60) NOT NULL,
   COL_A        VARCHAR(60) NOT NULL,
   TBL_B        VARCHAR(60) NOT NULL,
   COL_B        VARCHAR(60) NOT NULL,
   DIRECTION    VARCHAR(4)  NOT NULL,
   CLASS_ID     VARCHAR(1000),          /* Used to instantiate handler! */
   REMARKS      VARCHAR(2000)
);

CREATE TABLE ASSOC_KEYWORD
(
   KW_ID        NUMBER(8)   NOT NULL,   /* SEQUENCE# USED BY ASSOC_LINK */
   KEYWORD      VARCHAR(60) NOT NULL,   /* DATA! */
   DESCRIP      VARCHAR(240)
);

CREATE TABLE ASSOC_XPTR
(
   XP_ID        NUMBER(8)   NOT NULL,   /* SEQUENCE# USED BY ASSOC_LINK */
   XP_TYP       VARCHAR(8)  NOT NULL,   /* IDREF, XPointer expression, etc. */
   HREF         VARCHAR(1000) NOT NULL,
   DESCRIP      VARCHAR(240)
);

CREATE TABLE ASSOC_TOPIC
(
   TOPIC_ID     NUMBER(8)   NOT NULL,   /* SEQUENCE# USED BY ASSOC_LINK */
   DESCRIP      VARCHAR(240)
);

With these tables you can:
        Create linear lists of topics, keywords and pointers,
        Associate pointers with topics,
        Associate keywords with topics,
        Associate keywords with pointers,
        Associate keywords with keywords (synonyms, antonyms, etc.)

This isn't too bad, but it assumes that you use generated sequence numbers
as a primary/unique key to each table participating in the association.
What you are really doing here is implementing the network data model on a
relational database.  If you need a network data model use one.  It will run
much faster and your code will be much simpler.  Raima and Platinum are two
vendors of hybrid relational/network databases that I know of.  Raima does a
decent job of making set association look like a SQL join.

With both database models, however, you still have the column problem,
though.  I doubt the above would be sufficient for real world document
repositories. 

Regards,
Charles Reitzel
creitzel@mediaone.net



xml-dev: A list for W3C XML Developers. To post, mailto:xml-dev@ic.ac.uk
Archived as: http://www.lists.ic.ac.uk/hypermail/xml-dev/
To (un)subscribe, mailto:majordomo@ic.ac.uk the following message;
(un)subscribe xml-dev
To subscribe to the digests, mailto:majordomo@ic.ac.uk the following message;
subscribe xml-dev-digest
List coordinator, Henry Rzepa (mailto:rzepa@ic.ac.uk)