[Nottingham] TALK: Relational databases, SQL and PostgreSQL.

[David Aldred]

On Saturday 13 January 2007 18:18, Ovid wrote:
> --- David Aldred <david at familyaldred.org.uk> wrote:
> > I'm possibly out of the depth I need to be in on this, but can you
> > say in what circumstances these issues actually matter?
>
> Whenever you're using databases (that's not meant as tongue in cheek,
> either).

Yet it doesn't seem to be justified by what follows....

>
> > For example, a website where inidivdual items of data is updated via
> > a browser (using forms passing input to a MYSQL table) for display
> > to the public - such as news items and forthcoming events.  Would
> > concepts such as normalisation be relevant, and if so what would
> > they contribute?
>
> Normalization, as Godfrey Nix pointed out, helps to eliminate duplicate
> data.  This is very important because it's very easy to data out of
> synch if you don't normalize.  However, it's also very easy to get bad
> data in your system if you don't allow transactions (something which
> MySQL's default 'MyISAM' format doesn't support).

I may be wrong, but I thought transactions were important where multiple data 
changes needed to be made and the data needed to remain consistent 
before/after the whole series.  If a single operation completes all the 
necessary changes, then what is the danger?

> Let's say you have a non-threaded message board and eight people are
> simultaneously rushing to get a 'first post'.  The programmer writes
> the code to select the 'max(reply_order)' and in a *second* SQL
> statement stores the reply with a reply_order of one greater than the
> selected reply_order.  The eight people might then all post their
> replies with the same reply_order, something you don't want.
>
> Of course, this seems silly as they can set that in one SQL statement,
> but code is often written this way and when logic starts to get
> complicated, more reasonable variations of this occur.  

So for more straightforward situations (like the one I described), what is the 
danger?

> In short, without going through a lot of examples to show the issues,
> normalization and ACID compliant databases
> (http://en.wikipedia.org/wiki/ACID) are critically important.

Sorry, but I'm still not seeing how this is critical where data updates are 
handled in single SQL statements.  

If related data lies in a number of tables, then it could be an issue, if the 
related data's mutual consistency is important - but again, that's not my 
situation. 

-- 
David Aldred