----- Original Message -----
From: "Ben Luey" <lueyb at gridley.ACNS.Carleton.edu>
To: <tclug-list at mn-linux.org>
Sent: Monday, July 16, 2001 7:45 PM
Subject: Re: [TCLUG] mysql / php question


> > I've seen it done both ways.  The first way is arguably the simplest,
> > but the second way to more true to the relational model of data.
> > Adding a event type code to distinguish the two types is not a bad
> > idea either, but not a requirement.
>
> Is it possible to have three databases: One would be the "glue"

I assume you mean three _tables_.

> UniqueID type
> 1 a
> 2 b
> 3 a
>
> table a would be
>
> 1 info1 info2
> 3 stuff stuff2
>
> table b would be
> 2 otherformatted stuff with more fields
>
>
> Then my script could foreach in table glue, if type=a, get id, query table
> a, do stuff, and if typ=b, get id, query table b, etc.
>
> The main problem with this is maintaining the unique id numbers that join
> these tables. Can I somehow link these tables in mysql so when I add an
> item in table b, an entry is added to table glue, or do I manually (ie
> with scripts) need to maintain the syncing of these tables. It is my
> (naive?) impression that it what a relational database is for, but I can't
> find info on mysql on linking tables like this.

You could accomplish this with triggers, however I am unsure if MySQL
supports triggers.  I know MySQL doesn't support foreign key constraints
(which would also be useful in this context), because the designers consider
data integrity secondary to performance.  A trigger is a "SQL" script that
gets executed when a insert, update, or delete operation is performed on the
table.  I put SQL in quotes because the content of the script generally
includes proprietary extensions to ANSI SQL, e.g. conditional and flow
control constructs like "if" and "while".

Mike