Re: How much of data constraints on database is enough?

On Tue, Jan 15, 2008 at 10:07:12PM -0800, Miho Ishikura wrote:
> Hi people!
>
> I have a question that I'm always not clear about.
>
> Let's say there is an application that talks to PostgreSQL database.
> And you want to add constraints around it. Constraints contains
> validations, business logic, data integrity.

One of E.F. Codd's great insights was in the title of his original
paper on RDBMSs, "A Relational Model of Data for Large *Shared* Data
Banks," (emphasis mine) which he wrote in 1969.

He noted that any useful database would be shared by at least two
applications, and so a single system--what he called a Relational
Database Management System--should be the single source of data
integrity.

If one of the applications has one model of data integrity and another
has a different model--or just a buggy implementation of the first
one--disaster results, as the data gets to a state where it follows
none of the applications' rules. It's much simpler to have just one
thing--the RDBMS--be the final arbiter of data integrity.

> I have worked on the applications that was very database driven
> before. We had a lot of constraints like foreign keys, unique keys,
> check constraints, triggers, functions... etc. It was very rich and
> quite fun. I would love to do that again if I have a chance. I
> understand that it can be done and SQL on Rails is no joke (!).

SQL on Rails <http://www2.sqlonrails.org/> is an excellent parody of
Ruby on Rails, but there are things like mod_libpq which closely
approximate it.

> Being said that, you also have choice to do the same in your
> application as well. Why do you want to have them in 2 places?
> Maybe some constraints are appropriate to be done in application,
> some should be on database. But where do you draw the line? Might
> as well do them all in application!?

In which application? There isn't a single one.

> What do you think?

It would be nice to be able to translate database constraints from SQL
to something like Javascript automatically. While this is in theory
possible via Turing equivalence, it's impractical as yet.

Perhaps with pljs <http://xen.samason.me.uk/~sam/repos/pljs/>, some of
this may become slightly easier, but at this stage, good communication
among DB people and the rest of the teams works best.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate