Re: pro- foreign key propaganda?

From: Sabine Dinis Blochberger <no.spam_at_here.invalid>
Date: Fri, 16 May 2008 09:46:32 +0100
Message-ID: <PoqdnbeQgPJ01LDVnZ2dnUVZ8sDinZ2d_at_novis.pt>


sinister wrote:

> I'm supervising the development of a database system with a web interface.
>
> I had built the DB backend using postgresql. The guy we hired to take over
> the project from me---I have non-IT duties to attend to---for various
> reasons decided to switch to MySQL and created his own tables (as well as
> redoing the web interface).
>
IMO, he shoulnd't have redone the database at all. What this tells me is that he only knows MySQL and is unwillig to expand his knowledge to other RDBMs.

> After a conversation involving two tables which I thought ought to be
> connected by a foreign key relationship, I went and checked his DB; I was
> worried that he didn't fully understand the importance of using foreign
> keys. So I ran mysqldump and then did "grep -i foreign dump.sql" and
> "grep -i references dump.sql." Nothing!
>
> So...sent him an email late in the day, stressing that it's important that
> foreign keys be used where possible, that as much of the model
> logic/business logic/whatever should be encoded in the database itself to
> ensure data integrity, and so on.
>
Did you create an ERD? That might be an easy(er) way to explain it to him.

> What do I do if he gets back to me and says---as I'm sure everyone in his
> position does---"oh, but my PHP code makes sure everything is done right"?

That is bovine excremental matter. He should know he is wasting his (and his paycheck writers) time re-inventing a wheel that is already running smoothly. <g>

> Not that I don't know what to say, but it would be good to have a pointer to
> an essay or webpage that succinctly explains why you're asking for trouble
> if you don't encode such relationships in the DB itself.
>
That's easy. Ask "What happens if someone accesses the database through something different then the website?". Then all the "make sure" code is rendered utterly useless.

Then there's the problem MySQL has with ttransactions - I don't know the details, but seems there are some shortcommings.

> Sure, I could say, "Your next assignment is to read a book on DB theory, and
> give me a 10 minute summary," but we don't have that much time...
>
No time for education? Then they shouldn't hire newbies, sorry. Beside, he already wasted time by re-doing your work. And if he keeps the direction he's going, they are going to lose *alot* of time fixing bugs when the system goes into production.

Our company, for example, was doing a database with web frontends for workers and customers (for a real estate business). My boss started out with dBase, and he also put the "safeguards" into code rather than the db definition. Needless to say, it went downhill pretty fast once the workers all accessed simultaneously (the dbase process would hang itself and consequently the webserver). We changed to FirebirdSQL, and that's when we could stop worrying about the database and focus on the user end.
There was alot of pain coming our way from the customer (understandibly), because the system was getting unusable.

Even if your project is a small company, you should always have a bigger picture and future growth in mind. You would want your client to grow as a result of a better system. (in the above example, the client was expanding to other countries, and the extranet system we started did help. They since hired someone else, but it was my boss giving up on an annoying customer). Received on Fri May 16 2008 - 10:46:32 CEST

Original text of this message