Be the first to write a review
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.
Matt Machell
A 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.