Re: pro- foreign key propaganda?

"Sabine Dinis Blochberger" <no.spam_at_here.invalid> wrote in message news:PoqdnbeQgPJ01LDVnZ2dnUVZ8sDinZ2d_at_novis.pt...
> sinister wrote:

Thanks for your reply and interesting comments.

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

He was actually willing to use PostgreSQL. What happened is that he wanted to use a content management system, and the CMS of his choice is only starting to allow the use of postgres as a backend.

His desire to use a CMS is actually, in retrospect, a bad decision I think, because we don't need a CMS, just a web front-end for our DB, and CMS's are mainly (AFAICT) built for the situation where you're doing web hosting and don't have real control over things. E.g. instead of using the RDMS's own access control and privilege system, the CMS has its own.

But that's OT.

:-)

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

I think he understands the concepts, just not why it's important.

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

That's a very interesting issue. We had a guy who was very green, so he was only learning and I was writing all the code. So we let him go and a few months later we hired this new guy. I have other duties (scientific, non-IT), so my boss didn't want me spending that much time on this in the long run. I warned him that if we hired someone new, he'd want to discard my work and do his own.

Which is what happened. At least the guy agreed with the basic architecture (opensource RDMS backend, apache/PHP-driven frontend). But in terms of actual details he did redo everything except for the server itself (OS and Apache).

I could have told him he had to work within my design, but I'm not a programmer by trade (do it on the side in the course of my scientific duties). I've noticed that my attitude is pretty different from his, and from what I see on the web (e.g. learning about the CMS): mine is (a) "keep it simple, stupid," and (b) validity and efficiency of design are far more important than appearance (of the interface). Looking at his work and the stuff you see on the web, seems like a lot of programmers---well, at least web programmers---don't agree with that.

The mere fact that so many web hosting companies provide MySQL rather than postgresql shows me that someone is wrong out there---if web designers using RDMS's really knew what they're doing, they'd demand access to postgres.

They'd also never leave foreign keys out of their designs, too, I guess.

:-)

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

That's a good point I hadn't thought about. I do access the DB through the backend all the time, and I shudder at the thought of not having fk constraints.

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

I think MySQL is nominally a lot better than it was before because now you have InnoDB. I wouldn't trust MySQL, however, because the people who started the project at the beginning clearly had no understanding about RDMS's.

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

I agree...that's why I do think the backend should be the primary focus. When I was doing the main coding, it amazed me that most of the labor (> 85% I estimate) went into the frontend (PHP coding), yet the backend provided so much power. And while naive users can't do much of anything using simple client access to the backend---I have a hard enough time teaching them simple Linux commands---I could do anything I wanted to, very easily Received on Fri May 16 2008 - 15:04:49 CEST