8 Sep 2003 dsnopek   » (Journeyer)

I give this a big "Argh!"

I decided it was finally time to add multi-layer joins to Xmldoom because I needed to create MANY-TO-MANY relationships.  For example:

<table name="orders">
    <columns>
        <int name="order_id" unsigned="true" auto="true" primary-key="true"/>
        <datetime current="true"/>
    </columns>
</table>
<table name="items_ordered">
    <columns>
        <int name="order_id" unsigned="true"/>
        <int name="item_id" unsigned="true"/>
    </columns>

    <!-- TODO: could use a more succinct syntax? -->
    <join column="order_id" link="orders.order_id" relationship="parent"/>
    <join column="item_id" link="items.item_id" relationship="parent"/>
</table>
<table name="items">
    <columns>
        <int name="item_id" unsigned="true"/>
        <string name="description" length="80"/>
    </columns>
</table>

Anyway, we would obviously want to be able to retrieve the items on the order.  This is where the multi-layer join comes in.  We ask definition, "How do I get from orders to items?"  The answer is:  

orders.order_id -> items_ordered.order_id
items_ordered.item_id -> items.item_id

In the end, we'd have a Order.GetItems() method that would return the items on the order, right?  Well, not exactly.  What if items_ordered contained more data about each item ordered?  For example, the quantity or the sale price.  Not only common but almost essential.  So I could almost see the need for a new object: ItemOrdered with properties for Quantity and SalePrice.  But this isn't exactly what we want because an object must have a single object key.

Here are a number of options:

  • Allow an object to be identified by two keys. This also opens the possibility for simply declaring the keys in the table and <object-key ...> will only refer to a table. This would also remove the need for <unique/>.
  • Performing the two-layer join but also merging the Item object with the ItemOrdered object so that we can get all of its properties without another query. Or maybe return a dict or tuple containing the packed Item object and the extra ItemOrdered data? Maybe the ability to have ItemOrdered be a descendant of the Item object?


The answer will probably be a combination of the above ideas.  I am just upset that I never thought of this situation before now.

Latest blog entries     Older blog entries

New Advogato Features

New HTML Parser: The long-awaited libxml2 based HTML parser code is live. It needs further work but already handles most markup better than the original parser.

Keep up with the latest Advogato features by reading the Advogato status blog.

If you're a C programmer with some spare time, take a look at the mod_virgule project page and help us with one of the tasks on the ToDo list!