PostgreSQL & PHP Tutorials - Database Joins - Part I

PostgreSQL »  Introduction to Database Joins
PostgreSQL »  Starting Out »  Introduction to Database Joins

Posted By Chris Smith Posted on 02 Apr 2006, 08:34 AM
Viewing page 1 of 4
« | Back | Next | »

Setting up multiple tables in a database is an easy way to keep related data together. We keep news separate from authors, blogs separate from news, etc. Not only is this more managable from our point of view (we can quickly see which table contains what information), but all of our data will be in better shape also.

If we didn't separate the data then we'd have to keep the author information with the news item and store the blog information separately - it's not practical and very prone to errors. If you change an authors email address in one place, then you might forget to change it somewhere else.

Keeping the data separate like this is called "database normalization" and is quite a complicated topic in itself, so we'll leave that alone for now.

Back to the topic at hand!

In an example CMS setup, we have an authors table that looks like this:

CREATE TABLE authors (
authorid INT PRIMARY KEY,
username VARCHAR(255),
firstname VARCHAR(255),
lastname VARCHAR(255)
);

... and another table for news which looks like this:

CREATE TABLE newsitem (
newsid INT PRIMARY KEY,
newstitle VARCHAR(255),
newscontent TEXT,
authorid INT REFERENCES authors(authorid)
);

We need to create the authors table first because the "newsitem" table makes a reference to it and the database should check to make sure it's available so it can set up the relationship.

How do we get the authors name for a particular news item? We have to join the tables together. Note that both tables (in this example at least) have a column called 'authorid'. In the 'authors' table this is a primary key so we can uniquely identify each row.

In most cases, when you join tables together in a query you should be using a primary key - or at least a unique key - from one of the tables.

The fields need to be indexed so postgresql can quickly join them together.
Viewing page 1 of 4
« | Back | Next | »

Avg Rating: 3
Vote Count: 11


              

  New Reply


good tutorial but the inner join are very slow realizzazione siti 07 Jun 2007 Reply
`-- Then you're missing an index or something. They should be quite fast. Chris Smith 16 Jun 2007 Reply


Want to post a comment? Fill in the details below.

Your Name  : 
Your Email  : 
Your Website  : 
Spam Check! Please answer this question  : 7 + 5 =
Comment