XML-to-Relational Mapping for Complex Elements with Multiple Parents

H

harrisloris

New Member
I asked this question recently at Flattening XML Data into a database schema with XMLSpy, but I'm still struggling with it. It's also not really an XMLSpy question, so I'm reposting it in a different way.What is the best or most standard way to map XML elements to database tables when the element can occur in multiple places in the hierarchy?For example:\[code\]<?xml version="1.0" encoding="UTF-8"?><a> quux <b> corge <c> grault <d> garply <e> waldo <f>foo</f> </e> </d> <e> fred <f>bar</f> </e> </c> <e> plugh <f>baz</f> </e> </b> <e> xyzzy <f>qux</f> </e></a>\[/code\]Note that the \[code\]<e>\[/code\] element (which is complex, since it contains an \[code\]<f>\[/code\] element) can be a child of \[code\]<a>\[/code\], \[code\]<b>\[/code\], \[code\]<c>\[/code\], or \[code\]<d>\[/code\].It's impractical to map this to a table structure of:
  • A: (a_pk, a)
  • B: (b_pk, a_fk, b)
  • C: (c_pk, b_fk, c)
  • D: (d_pk, c_fk, d)
  • E: (e_pk, ?_fk, e, f)
Because E is actually a child table of multiple tables (A, B, C, and D).What's the best way to fix this?[*]Create multiple E tables, one for each possible association:
  • A: (a_pk, a)
  • E_A: (e_pk, a_fk, e, f)
  • B: (b_pk, a_fk, b)
  • E_B: (e_pk, b_fk, e, f)
  • C: (c_pk, b_fk, c)
  • E_C: (e_pk, c_fk, e, f)
  • D: (d_pk, c_fk, d)
  • E_D: (e_pk, d_fk, e, f)
[*]Add a fk to E for each possible association:
  • A: (a_pk, a)
  • B: (b_pk, a_fk, b)
  • C: (c_pk, b_fk, c)
  • D: (d_pk, c_fk, d)
  • E: (e_pk, a_fk, b_fk, c_fk, d_fk, e, f)
[*]Reverse the indirection (this works only when \[code\]<e>\[/code\] occurs 0-1 times):
  • A: (a_pk, a, e_fk)
  • B: (b_pk, a_fk, b, e_fk)
  • C: (c_pk, b_fk, c, e_fk)
  • D: (d_pk, c_fk, d, e_fk)
  • E: (e_pk, e, f)
[*]Add a "parent" indicator to E (example: (2, "A", 1, 69, 42), meaning that "the foreign key of 1 refers to the record in table A with a primary key of 1"):
  • A: (a_pk, a)
  • B: (b_pk, a_fk, b)
  • C: (c_pk, b_fk, c)
  • D: (d_pk, c_fk, d)
  • E: (e_pk, parent_table, fk, e, f)
[*]Consider it a many-to-many association, and build a mapping table for each possible parent:
  • A: (a_pk, a)
  • A_E: (a_fk, e_fk)
  • B: (b_pk, a_fk, b)
  • B_E: (b_fk, e_fk)
  • C: (c_pk, b_fk, c)
  • C_E: (c_fk, e_fk)
  • D: (d_pk, c_fk, d)
  • D_E: (d_fk, e_fk)
  • E: (e_pk, e, f)
[*]Something else?None of these seems ideal to me.
 
You must log in or register to reply here.
Top