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.
Read More