[ic] Database design questions

[Mike Heins]

Quoting Dan B (db@cyclonehq.dnsalias.net):
> You might dig up the short thread from last year 
> about Mike Heins' normalization preferences.  :-)

As I am wont to, I repeat them frequently. 8-)

> 
> As far as foreign keys, you can easily add foreign keys to your given 
> database (dbconf/... files).  But I imagine it's not in the distributed 
> template because there is no cross-database compatibility-layer for foreign 
> keys.  I.e., oracle does foreign keys with command, pgsql with yet another, 
> etc., ad nauseam.
> 
> However, Mike recently made another amazing feat when he skillfully added 
> the beginnings of cross-platform support for transactions in 4.7.x.  Last I 
> heard, it has only been tested on pgsql, but I imagine someone will do it 
> on Oracle soon.

It is tested on Oracle as well. Including checking that
orders/transactions/userdb updates roll back on an error.

> 
> Therefore, it would be considerable that someone Foreign keys could be 
> built in.  One problem with foreign keys is that MySQL does not support 
> them (unless the new berkeleydb backend does, anyone know?).  But the value 
> of adding foreign keys is clearly seen by all the questions like: "Why when 
> I delete from products is it still in the other tables, like pricing and 
> inventory)?"

MV 4.7.x has a foreign-key extension to the data tag:

	[data table=foo column=bar key=baz foreign=buz]

That ends up doing

    select bar from foo where buz = 'baz'

with a virtual "LIMIT 1" and works on all database types. We have added
a DEFAULT and DEFAULT_SESSION setup so that you can do inserts with
tags like that and initialize entries that would otherwise need to be
set to honor foreign-key constraints.

And if you get stuck using the standard stuff, you can always drop into
direct DBI to do the record initialization you need.

> 
> Foreign keys support would nip that in the bud, as well as increase 
> all-around integrity of the data in the database.  I don't think RH 
> (Akopia) lives in a glass house either, so it would probably be fine to 
> submit patches for clearly better schemas, if one were to come out of this 
> thread.

Next up is a built-in portable AUTO_NUMBER selector for sequences (for
dbs that support that, anyway).

Bottom line is, you can do relational stuff quite easily in Interchange.
If you really need to. 8-)

-- 
Red Hat, Inc., 131 Willow Lane, Floor 2, Oxford, OH  45056
phone +1.513.523.7621 fax 7501 <mheins@redhat.com>

Being against torture ought to be sort of a bipartisan thing.
-- Karl Lehenbauer