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

[Ovid]

--- 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).

> 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).

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.  Of course, you
could argue that the 'reply_order' needs a unique constraint, but that
just means that 7 out of the 8 posters are probably going to get an
error message.  However, if you have transactions, every post gets its
own transaction and these problems *go away*.

(That's a thoroughly contrived example, but the example I thought of
using shopping carts, customer orders and deleted items was getting to
be a pain).

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.  Many
programmers are great at writing excellent code but they forget that
the database can be thought of as code, too.  Write either of them
poorly and you'll suffer from buggy, difficult to maintain systems.

Cheers,
Ovid

--

Buy the book -- http://www.oreilly.com/catalog/perlhks/
Perl and CGI -- http://users.easystreet.com/ovid/cgi_course/