Learning MySQL: Designing Databases

Tuesday, January 5, 2010 , Posted by Johnny Fuery at 4:21 PM

Originally Published 2007-08-08 11:57:12

A friend of mine is learning PHP and MySQL development the same way I did: by noting a needed application and setting out to write it. I suppose I had a slight advantage, since I have a programming background (PHP is a lot easier to learn if you already know, say, Perl, and understand the basics of the HTTP protocol), but the premise is the same.

In my experience, learning occurs most efficiently when there is a pressing need, a personal interest, and the ability to realize results quickly. In other words, you need an application for your knowledge in order to absorb it well. That could just be my own style of learning, of course.

At any rate, here are a few notes from our email interactions:

I have bought two books on PHP/MySQL. One is probably written for the
beginning programmer, someone who's already somewhat familiar with other
programming language structures, but not with these. "SAMS Teach
Yourself PHP, MySQL, and Apache
." The second assumes you know the
basics about PHP/MySQL for the purposes of using its functions/code.
"PHP & MySQL Everyday Apps for Dummies."

I've started reading upon MySQL... and rather than starting me with
"here's how you create a DB" they are starting with, "here's how you
design db structures to work with what you want." Its like reading the
instruction manual before you start putting IKEA furniture together.
Probably not necessary, but probably good for structural integrity.

In any case, its describing exactly why my method of designing db's in
PHP using a file composed of arrays is a terrible idea. And, frankly,
they're right. My one concern is that if I really do log every [interaction], I'll end up with a ginormous table that is redundant anyhow.

I responded:
Just archive your gia-normous logging table when it gets unwieldy. Since your interactions are pretty limited (lots of writes, but not a lot of selects/joins) performance won't be a big deal until you're well over many millions of rows.

Don't worry about it. Just design your DB with scalability in mind (i.e., fully normalized, with no joins if possible) and you'll be fine. You probably won't need to dump your table even past 10mm rows if you add a tinyint flag indicating archival (or whatever moniker).

Like posts like this? Sound off by commenting or drop me a note with a question.

Currently have 0 comments:

Leave a Reply

Post a Comment