Loading ...
Sorry, an error occurred while loading the content.

One table per element type (regardless of context) - possible?

Expand Messages
  • Katarzyna Marszalek
    Is it possible, using version 2.0 of XML-DBMS, to map an element type that can occur as child of many different element types, to one table? For example,
    Message 1 of 3 , Feb 3, 2003
    • 0 Attachment
      Is it possible, using version 2.0 of XML-DBMS, to map an element type that
      can occur as child of many different element types, to one table?

      For example, having this DTD, where b can occur in both a1 and a2:

      <!ELEMENT a1 (b+)>
      <!ATTLIST a1
      id CDATA #IMPLIED
      >

      <!ELEMENT a2 (b+)>
      <!ATTLIST a2
      id CDATA #IMPLIED
      >

      <!ELEMENT b (#PCDATA)>
      <!ATTLIST b
      parentid CDATA #REQUIRED
      id CDATA #IMPLIED
      >

      one could create three tables:

      CREATE TABLE a1 (
      id INTEGER NOT NULL, CONSTRAINT id PRIMARY KEY (id)
      );

      CREATE TABLE a2 (
      id INTEGER NOT NULL, CONSTRAINT id PRIMARY KEY (id)
      );

      CREATE TABLE b (
      parentid INTEGER NOT NULL,
      id INTEGER NOT NULL, CONSTRAINT id PRIMARY KEY (id)
      ...
      );

      and having a1 of this XML fragment:

      <a1 id="111">
      <b id="998" parentId="111">
      <b id="999" parentId="111">
      <a1>

      retrieve the b children, using this SQL command:

      SELECT * FROM b WHERE parentId=111;

      In my project, I don't ever need to retrieve a1 or a2 having b only (so it's
      fine not have the table name information of b's parent, although, in theory,
      that information could be stored in additional column.)

      It seems to me that, currently, the mapping language requires (through the
      <RelatedClass...> element,) the b element to be related strictly to only one
      table/element type, by the foreign key concept. Thus, instead of having one
      table for the b element (easily searchable, for example,) the mapping
      enforces creation of many tables (a1_b, a2_b, etc.) Is this (and will be in
      the future,) restriction really unbreakable? If so, why? Is there some
      other way, that I missed, to create just one table for such element?

      Regards,
      Katarzyna Marszalek
    • Ronald Bourret
      This restriction does not exist. You can map b as a related class of both a1 and a2. Normally, there would be two different foreign key columns in the table
      Message 2 of 3 , Feb 3, 2003
      • 0 Attachment
        This restriction does not exist. You can map b as a related class of
        both a1 and a2.

        Normally, there would be two different foreign key columns in the table
        for b -- one for the foreign key from a1 and one for the foreign key
        from a2. This requirement comes from SQL, which allows a foreign key to
        point to only one primary key.

        However, XML-DBMS does not have this restriction. That is, you can use
        the same column (parent_id in your case) as the foreign key for both a1
        and a2. The only thing you need to be certain of is that there is no
        overlap between the primary key values in a1 and a2. This would cause
        problems when retrieving data from table b, as it would get too many
        rows.

        -- Ron

        Katarzyna Marszalek wrote:
        >
        > Is it possible, using version 2.0 of XML-DBMS, to map an element type that
        > can occur as child of many different element types, to one table?
        >
        > For example, having this DTD, where b can occur in both a1 and a2:
        >
        > <!ELEMENT a1 (b+)>
        > <!ATTLIST a1
        > id CDATA #IMPLIED
        > >
        >
        > <!ELEMENT a2 (b+)>
        > <!ATTLIST a2
        > id CDATA #IMPLIED
        > >
        >
        > <!ELEMENT b (#PCDATA)>
        > <!ATTLIST b
        > parentid CDATA #REQUIRED
        > id CDATA #IMPLIED
        > >
        >
        > one could create three tables:
        >
        > CREATE TABLE a1 (
        > id INTEGER NOT NULL, CONSTRAINT id PRIMARY KEY (id)
        > );
        >
        > CREATE TABLE a2 (
        > id INTEGER NOT NULL, CONSTRAINT id PRIMARY KEY (id)
        > );
        >
        > CREATE TABLE b (
        > parentid INTEGER NOT NULL,
        > id INTEGER NOT NULL, CONSTRAINT id PRIMARY KEY (id)
        > ...
        > );
        >
        > and having a1 of this XML fragment:
        >
        > <a1 id="111">
        > <b id="998" parentId="111">
        > <b id="999" parentId="111">
        > <a1>
        >
        > retrieve the b children, using this SQL command:
        >
        > SELECT * FROM b WHERE parentId=111;
        >
        > In my project, I don't ever need to retrieve a1 or a2 having b only (so it's
        > fine not have the table name information of b's parent, although, in theory,
        > that information could be stored in additional column.)
        >
        > It seems to me that, currently, the mapping language requires (through the
        > <RelatedClass...> element,) the b element to be related strictly to only one
        > table/element type, by the foreign key concept. Thus, instead of having one
        > table for the b element (easily searchable, for example,) the mapping
        > enforces creation of many tables (a1_b, a2_b, etc.) Is this (and will be in
        > the future,) restriction really unbreakable? If so, why? Is there some
        > other way, that I missed, to create just one table for such element?
      • Katarzyna Marszalek
        ... Yes, thank you, I got it working: I did not suspect that within a element one can use a number of s, with different names, and
        Message 3 of 3 , Feb 3, 2003
        • 0 Attachment
          Ronald Bourret wrote:

          >This restriction does not exist. You can map b as a related class of
          >both a1 and a2.
          >
          >Normally, there would be two different foreign key columns in the table
          >for b -- one for the foreign key from a1 and one for the foreign key
          >from a2. This requirement comes from SQL, which allows a foreign key to
          >point to only one primary key.
          >
          >However, XML-DBMS does not have this restriction. That is, you can use
          >the same column (parent_id in your case) as the foreign key for both a1
          >and a2. The only thing you need to be certain of is that there is no
          >overlap between the primary key values in a1 and a2. This would cause
          >problems when retrieving data from table b, as it would get too many
          >rows.

          Yes, thank you, I got it working: I did not suspect that within a <Table...>
          element one can use a number of <ForeignKey...>s, with different names, and
          map them to the _same_ column through the <UseColumn...> element (one needs
          only to remove the corresponding CONSTRAINTs from the resulting sql, if
          generated.)

          Regards,
          Katarzyna Marszalek
        Your message has been successfully submitted and would be delivered to recipients shortly.