Here's a simple example: consider what happens if you are creating a database of book titles and one of your fields is for author. You, being a database-savvy kind of monk, know that you want a separate table for authors. If you hard-code the author's name into 30 book records and then you discover that you've misspeeled [sic] the name, you need to update 30 records. With the author in a separate table and the book record pointing to the author's record in the "author" table, you only have one place to update the author's name.

But wait! What about collaborations or anthologies? You could have several authors for one book and your "author_id" field in the "book" table doesn't work. You actually have what's called a "many-to-many" relationship and with most relational databases (multi-value databases are an exception) you need to create a new table that both the "author" and "book" table point to (you'll understand why when you read about normalizing).

As much as I hate to mention Microsoft, they have a nice overview of the basics.

Cheers,
Ovid

Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

Just a addendum to your post: Denormalisation is also a used technique. Look at datawarehouse design...
But I agree that the best way is to start learning the normal relational database model.

But let's face it that for writing a forum or a guestbook you don't really need either of them :)



--
My opinions may have changed,
but not the fact that I am right

this isn't too tricky actually, it being firstly about the only question so far that I have been able to answer grins and secondly about the first thing I did with Perl when I learned it all of six months ago! If you go to the great library in the sky, CPAN and look up database modules and then click on the listing of modules by category and look for DBM.pm (or just click here) you can download DBI.pm.

BUT best of all Perlmonks has, under the heading of tutorials, lots of tricks 'n' tips on fiddling with databases. Go here.

This was a public service broadcast brought to you by Elgon. Thankyou.

Well, it ain't the book review section, but here's my mini-review of MySQL and mSQL: it's not a bad introduction to the basics of databases, incl. normalization and application architecture -- but I'm willing to bet that, other than the normalization stuff, most of those who are interested in "learning databases" don't need a lot of what it has to offer there -- basically, it has a lot of *very* short chapters on how to access MySQL and mSQL DBs from various types of languages, and often it doesn't get past providing a *reference* for the MySQL API for that language.

I'm more a fan of Programming the Perl DBI, co-written by Tim Bunce and Alligator Descartes; the former wrote a bunch of the DBD modules (drivers for specific types of databases) and maintains the DBI module. It takes you from flat files to 'real' DBMSen (some will tell you MySQL is not a 'real' database -- phbt! -- it does what many ask of it, and quite well; it stores information, therefore it's a database! -- herendeth the rant), and contains a pretty good reference on the DBD modules for various DB servers (Oracle, MySQL, Sybase, etc.). So let me second the nomination for that.

IMO, once you've got the basic idea behind the "prepare, bind/execute" cycle down (if it's not clear what that means, hopefully it will become so if you look at such sources as the Tutorials on this site (I like this one -- and check this other new-fangled perl site for what I think is a pretty good DBI tutorial by some other monk), the DBI module documentation is actually pretty helpful. If you go beyond MySQL to something like (gasp!) Oracle, one thing that will help you is ... well, nothing will help you =)

(OK, that last comment is definitely the result of me wrestling with Oracle all day today. Feel free to disregard that one =)

Philosophy can be made out of anything. Or less -- Jerry A. Fodor

In my opinion the "MySQL and mSQL" book is a real low in O'Reilly's output. It's basically a rehash of the manual pages and seems incomplete and patchy.

Much better is Paul DuBois' "MySQL", published by New Riders.

--
<http://www.dave.org.uk>

"Perl makes the fun jobs fun
and the boring jobs bearable" - me

That O'Reilly book is quite good, but doesn't, to my mind at least, get into the issues of designing databases. (Not that it should, it's a programming guide)

I actually enjoyed reading the Practical SQL Handbook -- I found it to be a very clear, accessible introduction to the basics of designing and using SQL databases.

It also looks like there's a new book out by the same authors called: Practical SQL: The Sequel. So that might also be worth a look.

If you are using PostgreSQL, there is a test script which comes with PostgreSQL, Apache_Pg.pl. The first couple of pages I wrote which interface to PostgreSQL where just simple hacks of this.

I'm sure you'll have no trouble at all.

Just to add some non-Perl reference on databases: I found An Introduction to Database Systems to be very good.

Christian Lemburg
Brainbench MVP for Perl
http://www.brainbench.com

greetings,

I would recommend looking at:

WebReview.com: Database Developer

I especially enjoyed the article titled "From Chaos to Order: Designing Databases". I've been working with databases for quite a few years now, but found this article to be very clear.

Also, I like Philip Greenspun, and if you can get past the ego, I think there's good stuff there. One I might recommend is:

"SQL for Web Nerds"

I would also say get yourself a database to connect to, be it MySQL, MSSQL, PostgreSQL, whatever; and, start making databases!

I think actually building stuff is the quickest way to learn. A lot of the information collected in the many books on database design is very practical and when you start to build databases, even simple projects like the ubiquitous CD collection, all the issues of normalization, data types, how to do queries, etc. It all comes up. Being that Database books are usually so damn expensive, I would heartily recommend mucking about with a free database first and go for the book purchase when you just can't stand it anymore.

There are so many books on database design, I couldn't possible make a useful recommendation. I have the C.J. Date book, which another person recommended, but it's kind of crufty.

So, get yourself a database you can start plunking with, and it's either fun, and you keep doing it for years (like me), or you don't and you go eat some cookies (which is ok too).

cheers