[development] Referential integrity -- finally?

[Earnest Berry III]

I will agree from experiance. It is MUCH faster, when you get into larger
data sets, to drop FK/RI all together, and handle the logic at the
application level. However, "indexes" in general are another story. That
starts to become database specific and has to do with the ways in which you
can manipulate the query plan per DB implementation (Oracle, Sybase, MSSQL,
etc.). In general though, for mass inserts, it is faster to drop all
indexes, insert/update, and then re-create the index. I use this technique
on a couple apps with GREAT success.

I think the DB use issues, IMHO, comes from and depends on the applications
purpose. Most Drupal apps are blogs/news content/etc. and are READ/SELECT
intensive, thus most DB will do, and it's just as good to use MySQL, SQLite,
etc. However, when you get into transaction/OLAP/e-commerce heavy type apps
with many simultaneous UPDATE/INSERTS/transactions.....this, I find, is
where the scalability/usability of MySQL, comes into play.

With that said...I wonder if we should have an option to not use RI.
But...dont' get me wrong, I want RI in Drupal, and I think RI needs to go
into core, and the above situation I have discussed should be considered a
minority and something dealt with by the developer/enduser.


On 1/21/07, Khalid B <kb at 2bits.com> wrote:
>
> On 1/20/07, Karoly Negyesi <karoly at negyesi.net> wrote:
> >
> > > Drupal is kindof database independent. Though no-one has managed to
> > integrate
> > > sqlite because of 'things' (locking) that Drupal requires.
> >
> > My hopes are that SQLite support will happen with Drupal 6. Tadej
> > Ba&#353;a has written the code and he says he is running Drupal with it
> > since 4.5 which is quite believable. He also volunteers to be the
> > maintainer: http://lists.drupal.org/pipermail/development/2007-January/021806.html
> > My hopes are that Drupal will run out of the box, without any setup
> > whatsoever. LOCKs are on their way out, killes is nurturing the no lock
> > patch I abandoned. And even if they do not die, they are there only to avoid
> > some races which simply do not happen on the small sites that are targets
> > for SQLite (ie. quite small sites).
>
>
> Wasn't the other problem is that SQLite does not have some ALTER variant
> that we use, so updates to the schema not possible? Has this been recently
> solved too? If so, then good news.
>
>
>
> Back on topic, referential integrity is the way forward. Not only does it
> make us friends with the PostgreSQL folk, but it is good practice in general
> to have it, regardless of the underlying database engine.
>
> The debate about RI is a non-debate. It is a historical issue. There was a
> database used by hosting companies called mSQL (mini SQL) and it was not
> free, MySQL stepped in to fill the gap with a free database (as in cost-free
> initially), with the same syntax, ...etc.. MySQL's popularity skyrocketed,
> then it became free (as in liberty). They argued that RI is not necessary
> since web apps are mostly read intensive, and they shined in the read
> intensive arena.
>
> Now, true databases all have RI. MySQL themselves had InnoDB, and most
> hosting companies for the last 2 years or so support InnoDB. If only Oracle
> would not have nabbed that, it would have been the way forward. Now they are
> developing Falcon and solidDB, ...etc.
>
> Regardless, RI is needed.
>
> +1.
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/development/attachments/20070121/28748207/attachment.htm