I'm working on importing a large XML file into a mySQL database.
Without going into too many irrelevant details, the XML schema
includes a cross-reference field that can refer to a unique entry in
either of two separate tables. As a trivial example:

:
<fruit>
<fruit_name>apple</fruit_name>
<xref>potato</xref> <!-- Cross-reference to "vegetable" element -->
</fruit>
<fruit>
<fruit_name>orange</fruit_name>
<xref>apple</xref> <!-- Cross-reference to "fruit" element -->
</fruit>
:
<vegetable>
<vegetable_name>potato</vegetable_name>
</vegetable>
:

Here, the content of the <xref> element can be the primary key of a
<fruit> element or a <vegetable> element. Instead of storing the text
value of these <xref> elements, I want to store a value that uniquely
identifies the element it refers to. Something like this perhaps:

:: Table `fruit`
+----------+-----------------+-----------+
| fruit_id | fruit_name | xref |
+----------+-----------------+-----------+
| 1 | apple | 3 |
+----------+-----------------+-----------+
| 2 | orange | 1 |
+----------+-----------------+-----------+

:: Table `vegetables`:
+----------+-----------------+-----------+
| veg_id | veg_name | xref |
+----------+-----------------+-----------+
| 3 | potato | NULL |
+----------+-----------------+-----------+

Ideally I'd like to somehow link the ID values in the `fruit` and
`vegetable` tables so that when one is incremented, the other is also
incremented. Is this possible?

Alternatively I guess I could use a separate xref column for each type
of target. But this doesn't seem to be very efficient because one of
these IDs will always be null (they can't both be set).

A third option would be to combine the "fruit" and "vegetable" data
into a single table, but in the XML file I'm working from, the "fruit"
and "vegetables" are very different and combining them would result in
a lot more redundancy.

I hope this all makes sense. Can anyone suggest the best way of fixing
this?

On Feb 7, 10:37 am, philro...@blueyonder.co.uk wrote:
> I'm working on importing a large XML file into a mySQL database.
> Without going into too many irrelevant details, the XML schema
> includes a cross-reference field that can refer to a unique entry in
> either of two separate tables. As a trivial example:
>
> :
> <fruit>
> <fruit_name>apple</fruit_name>
> <xref>potato</xref> <!-- Cross-reference to "vegetable" element -->
> </fruit>
> <fruit>
> <fruit_name>orange</fruit_name>
> <xref>apple</xref> <!-- Cross-reference to "fruit" element -->
> </fruit>
> :
> <vegetable>
> <vegetable_name>potato</vegetable_name>
> </vegetable>
> :
>
> Here, the content of the <xref> element can be the primary key of a
> <fruit> element or a <vegetable> element. Instead of storing the text
> value of these <xref> elements, I want to store a value that uniquely
> identifies the element it refers to. Something like this perhaps:
>
> :: Table `fruit`
> +----------+-----------------+-----------+
> | fruit_id | fruit_name | xref |
> +----------+-----------------+-----------+
> | 1 | apple | 3 |
> +----------+-----------------+-----------+
> | 2 | orange | 1 |
> +----------+-----------------+-----------+
>
> :: Table `vegetables`:
> +----------+-----------------+-----------+
> | veg_id | veg_name | xref |
> +----------+-----------------+-----------+
> | 3 | potato | NULL |
> +----------+-----------------+-----------+
>
> Ideally I'd like to somehow link the ID values in the `fruit` and
> `vegetable` tables so that when one is incremented, the other is also
> incremented. Is this possible?
>
> Alternatively I guess I could use a separate xref column for each type
> of target. But this doesn't seem to be very efficient because one of
> these IDs will always be null (they can't both be set).
>
> A third option would be to combine the "fruit" and "vegetable" data
> into a single table, but in the XML file I'm working from, the "fruit"
> and "vegetables" are very different and combining them would result in
> a lot more redundancy.
>
> I hope this all makes sense. Can anyone suggest the best way of fixing
> this?

Add a new table -- call it, say, "edibles".

Columns: unique_seq, edible_type

As you add fruits, add (new unique_seq, 'fruit'), to this table, then
set 'fruit_id' to that new unique sequence for the fruit table also.

As you add vegetables, add (new unique_seq, 'vegetable') to this
table, same with sequence again for the 'veg_id' column.

And this is expandable: meats, deserts, poisons, regiftable fruitcake,
etc.

The relationship is:

fruits IS-A edibles
vegetables IS-A edibles
meats IS-A edibles

On 8 Feb, 05:35, ThanksButNo <no.no.tha...@gmail.com> wrote:
>
> Add a new table -- call it, say, "edibles".
>
> Columns: unique_seq, edible_type
>
> As you add fruits, add (new unique_seq, 'fruit'), to this table, then
> set 'fruit_id' to that new unique sequence for the fruit table also.
>
> As you add vegetables, add (new unique_seq, 'vegetable') to this
> table, same with sequence again for the 'veg_id' column.

Ah, of course. I had a feeling I was missing something obvious.
Sometimes I just can't see the wood for the trees.

Many thanks.

Have yourself a slice of regiftable fruitcake.