Re: pro- foreign key propaganda?


"Sabine Dinis Blochberger" <no.spam@xxxxxxxxxxxx> wrote in message
news:PoqdnbeQgPJ01LDVnZ2dnUVZ8sDinZ2d@xxxxxxxxxxx
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


.

Relevant Pages

  • Re: pro- foreign key propaganda?
    ... I had built the DB backend using postgresql. ... that foreign keys be used where possible, that as much of the model ... If he is that much of a newb, make sure he is not using MyISAM. ... didn't declare any integrity because he doesn't think mysql will enforce ...
    (comp.databases.theory)
  • Re: pro- foreign key propaganda?
    ... I had built the DB backend using postgresql. ... 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. ... If he is that much of a newb, make sure he is not using MyISAM. ... Maybe he didn't declare any integrity because he doesn't think mysql will enforce it in any case. ...
    (comp.databases.theory)
  • Re: MySQL vs. PostgresSQL
    ... A few years ago I worked on a project that had PostgreSQL as the backend, ... found it to be a very good database to work with, ... with the open source products so I can't even recall what features MySQL ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Effect of MySQL being acquired by Sun Micro
    ... PostgreSQL did, so new developers (who only knew how to boot into ... Windows) picked MySQL. ... I didn't know SQL support was added that late. ...
    (comp.lang.php)
  • Re: Python, xml, databases, ...
    ... I think you want to be clear whether you want a database or not. ... Any machine can run a service (such as MySQL or PostgreSQL), ... both MySQL and PostgreSQL rely on the Cygwin dll. ...
    (comp.lang.python)