MySQL and Multiple Tables: Part 1

When you're creating your PHP applications with Dreamweaver, it's necessary to have a good basic knowledge of how SQL works and how to get the information you want, from the tables you've got.

In this article, we're going to explore the mysteries of working with multiple tables, and the relationships they have between them. We'll start off by talking about how to define your data so each table has a logical relationship to each other table, then move on to using those relationships to create useful queries. It's aimed squarely at people who only have the basics of how to work with MySQL, and want to delve a bit further.

We're going to use a simple database about a CD collection to show off the concepts.

Know your Data

When you first start out, it's very easy to just dump bits of data about a concept into one table.

A typical table that somebody new to database might create would be:

CDs

Id (BigInt)

Name(Varchar 60)

Artist(Varchar 60)

Owner(Varchar 60)

Publisher(Varchar 60)

1

Regular Urban Survivors

Terrorvision

Matt

EMI

2

Appetite For Destruction

Guns and Roses

Matt

Geffen

Now, the problem with this is that you get a lot of repetition. Every time I add a new CD I re-type the Band, the Owner and the Publisher, if somebody different owns the CD I have to add a new CD with a different owner. This isn't very good for storage purposes (I'm storing the names multiple times), results in very big tables and just isn't very efficient. With just 5 columns this isn't too noticeable, but when we start adding more details like owner contact number, owner addresses, publisher addresses, the duplication will start to become really unwieldy.

$3.29
- OR -

Matt Machell

Matt MachellA man of many talents, Matt has been a web designer, technical editor, and jewellery picker. He is currently on contract for the Birmingham City University, producing pages for research centres.

He has tech-edited a dozen books on web design and development for glasshaus, Apress and Sitepoint.

He likes music with loud guitars and games with obscure rules.

His website can be found at: http://www.eclecticdreams.com

He lives in Birmingham with his girlfriend, Frances, and a horde of spider plants.

See All Postings From Matt Machell >>

Place your review about MySQL and Multiple Tables: Part 1




You must me logged in to write a review
Reviews

Be the first to place a review