Re: Migrating PostgreSQL database to MySQL/MS Access

On Aug 13, 2006, at 11:34 am, Dave Page wrote:
> I see a similar issue in my line of work. Many of the programmers
> (even
> those working for the larger software houses in the social housing
> sector)
> have started out their careers as housing offices or maintenance
> surveyors
> or whatever and have drifted into programming over the years. Their
> bosses
> (in fact, in some cases they are the bosses) demand database
> neutrality so
> they can run with whatever DBMS the client prefers and they simply
> take the
> simple option and include all RI and other constraints in the
> client or
> middle tier, partly because they don't know any better, but more
> these days
> because they don't get the time or resources to do the job
> properly :-(
>
> You can imagine the fun we sometimes have chasing down data errors!

Dave,

The attitude where I work is similar. Part of the problem is our
biggest app is like a plate of spaghetti... we have business rules
enforced everywhere from the Flash front-end (using ActionScript) to
the MSSQL back-end (Transact-SQL). It's anyone's guess what's left
in the C#. So on one hand it's easy to see why the developers would
like it all moved in one location.

Personally, I'm a data integrity fanatic - we've been bitten by
violated rules too often (my favourite is two tables used to store
two types of login, one must have even primary keys the other must
have odd ones :) we learnt after I naively did a bulk insert into
both tables that constraints to enforce bizarre things like this are
quite useful...). However, I can also see the advantage of collating
the business logic. We are trying to switch development to Ruby on
Rails which will help in that it enforces an MVC architecture
(whether the developers will actually use it correctly is another
issue), but then leaves you with the problem of having to duplicate
all the business rules (at least those that enforce purely integrity)
in the database, so they can be used for interactive GUI error
messages as well as integrity.

Out of this situation seems to come the following problems:

- developers don't care about database integrity because "the app
works without it"
- developers are therefore too lazy to even consider (learning the
skill of) writing database constraints
- because the code lacks any formal business rules, they are never
clearly and explicitly documented (so we know what they actually are!)

We also have the issue some of our customers are clueless business
types from the Church of Microsoft and don't understand that Postgres
is actually better AND cheaper than SQL Server. If any demand we use
SQL Server, you can double the complexity of including integrity.

I am the least educated person in my office (no degree) and I'm the
ONLY one who has any understanding of relational theory. I can't
understand how developers employed to created database-backed
applications think they can get anywhere with so little understanding
of what they are doing. Well actually, having seen lecture slides
from one of the developer's uni courses, I have a vague idea.

Any suggestions on how to correct this attitude (and consequently the
problems above) would be very welcome :) I have actually written a
10-page introduction to relational theory, based on the bad design
decisions in our apps. I intend to sit everyone round, nail their
feet to the ground, and not let them leave until they have at least
acknowledged every point. How well this is received - and how long
my job lasts afterwards :) - remains to be seen.

Ashley