Re: Primary and Foreign Keys

["Thomas B. Passin" <tpassin@home.com>]

[Bullard, (Len]
> A more fun question:
>
> Given a relational database for which one creates a
> parallel XML schema, how do others think key relationships
> should be described:
>
> 1.  Simple types with the insurance that corresponding
> values exist in both tables (doesn't seem very strong to me).
>
> 2.  Key/keyrefs
>
> 3.  ID/IDREFs (probably a non-starter given the caveat)
>
> 4.  XLinks
>
> Caveat:  It is usually the case that the schema will be broken
> up into multiple schema given that the original is
> for a very large (field and table wise) relational
> system. Also note, this is going from relational to
> XML Schema, not the other way around.
>

It's a fun question, and one I'm interested in.  It may not, however, be as
significant as it seems.

Why are there foreign keys?

1) To provide for relational integrity checks and operations.
2) To indicate the intended relationships between two tables.
3) To make join operations easier to specify.

Will any of these survive the translation to XML? Can relational integrity
be enforced after the translation (except by specially-crafted code)? If
not, it may not matter much how to translate them.

Also, a foreign key can involve dependent or an independent tables.  This, I
think, is of more practical significant.  A dependent table is more likely
to be translated into child elements of its associated independent table (so
maybe you don't need the keys at all), an independent table should usually
remain an independent element structure.

Another situation would arise if a table is a "join" table (Mike Gorman's
term is more charming, though: "intertwinkle" table).  That generally means
a many-to-many relationship.  The primary key for a join table is usually
compound, which leaves ID/IDRef out unless you use two of them in each
element.

Finally, there are often other tables that use compound primary keys.

I don't know that the answer would be the same for each of these different
situations.

a) If you need compound keys, you would use key/keyref.

b) If you have duplicate key values in different tables (very common), you
need key/keyref simply because ID/IDREF doesn't allow duplicates within one
document.  This is softened by the possibility that you may have to mung the
key values since they are often numeric but keys and IDs have to start with
a name character.  If you are going to mung the values anyway, you could
mung them differently for each table and end up with unique values after
all.

I suppose it might make a difference whether the XML were all going to end
up in a sngle document or not, too, since you could duplicate IDs across
documents.

c) If you don't have duplicate key values and you are translating lookup
tables, ID/IDREF would be OK but if you are able to use key/keyref they
would seem to be fine too (by "use" I mean that the processing software
knows how to handle key/keyref.  XLink doesn't yet, for example).

That's as far as I've gotten so far, but I'm going to be revisiting it again
over the next several weeks or a month.

Cheers,

Tom P