Jochen Friedrich wrote:
>
> Hi Greg,
>
> > Table sprawl is no big deal, as long as you design it properly and
> > provide the right indexes and primary key information. (I presume you
> > mean multiple, linked tables here)
>
> Usually, you can define integrity constraints in any decent database which
> prevent the various tables going out of sync. I'm not sure if this is
> handled correctly by MySQL, though.
MySQL does not have any integrity constraints, unfortunately.
However, it can be argued that integrity constraints are simply a
debugging aid :-). Your application should be "doing the right thing" to
begin with. (I'd still use them, but it is an interesting point)
> In our gxsnmp project we used an index
> value of 0 to mean not-existing which was accepted by MySQL but rejected
> e.g. by Oracle or Informix. After changing the non-existing to NULL, the
> tables are built correctly and (at least on Oracle or Informix) are
> protected from becoming inconsistent.
As a side benefit, NULL usually takes less storage, too. In another
email thread, we had discussed adding an artist to the tracks table --
we'd use NULL to represent "not assigned (refer to the disc's artist)".
NULL is usually best to use within databases.
Note: in your app MySQL allowed it simply because it didn't check :-).
Oracle/Informix did check, so they complained.
Cheers,
-g
-- Greg Stein, http://www.lyra.org/