Relational databases for people in a hurry

This is one of a set of quick guides for getting started with the different components of Pylons.

Introduction

Data for Pylons applications is often stored in a relational database such as MySQL, Postgres or SQLite. SQLite is probably the simplest to set up. See the SQLite homepage for help on how to do this.

Note: Pylons has libraries which mean that you do not have to use the database programming language (SQL) so it is not necessary to learn that just yet.

Using tables

A relational database uses tables to store your information. So, for example, the database may have the following table called 'people' with the following data in it:

id	name	email
0	Mr Jones	mrjones@example.com
1	Mr Brown	mrbrown@example.com

It is custom to label all columns and table names in lowercase, using underscores to link words
It is a good idea to include the 'id' column in every table that you make
The 'id' column contains a unique identifying integer for each row in the table
A column which is used as a unique identifier is known as the primary key

What makes a database relational?

One aspect of a relational database is that it seeks to store information in an extensible way.

If Mr Jones has more than one email, we could add another column to the table i.e.

id	name	email	email2
0	Mr Jones	mrjones@example.com	mrjones@home.com
1	Mr Brown	mrbrown@example.com

However if Mr Jones has ten emails, or more then we need a way to add an arbitrary number of emails to Mr Jones' profile. We do this using a separate many to one table.

Many to One

We define an ordinary new table named 'person_emails' with the following structure.

id	person_id	email
0	0	mrjones@example.com
1	0	mrjones@home.com
2	1	mrbrown@example.com

Now when the data for Mr Jones is retrieved from the 'people' table, Pylons can look in the 'person_emails' table and find all emails whose 'person_id' matches Mr Jones' 'id' of 0.

This is known as a many to one relationship (many emails to one person).

You may think this is a lot of work but relational databases and Pylons libraries make this quick and easy.

Many to one tables are just normal tables with a column that refers to the 'id' of another table
Columns that refer to 'id's in other tables are know as foreign keys
The foreign key is normally named after the table it refers to in the singular (ie. person not people) plus '_id'
We no longer need the 'email' column in the original table. This can be removed.

Many to Many

A relational database also seeks to reduce the amount of repeated information.

Lets say Mrs Jones joins the group and we also want to include a column for addresses.

id	name	email	address
0	Mr Jones	mrjones@example.com	12 Sunny Drive, Pleasantville
1	Mr Brown	mrbrown@example.com	32 Rose Hill, Pleasantville
2	Mrs Jones	mrsjones@example.com	12 Sunny Drive, Pleasantville

We want to reduce the amount of repeated information so we put the addresses in a new table names 'addresses'.

id	address
0	12 Sunny Drive, Pleasantville
1	32 Rose Hill, Pleasantville

We then create another table named 'people_addresses' as follows.

id	person_id	address_id
0	0	0
1	1	1
2	2	0

Now when Pylons wants to find the addresses associated with 'Mr Jones' it first looks at the table 'people_addresses' and finds all entries that match Mr Jones' 'id' it then uses the 'address_id' to link Mr Jones with the correct address.

You may wonder that this is a lot of work for a small saving, but as your database grows this may become significant. Also we can extend this in a similar way to the many to one table. For example lets say we want to allow the work address to be added as well as the home address. we modify the tables accordingly,

id	address
0	12 Sunny Drive, Pleasantville
1	32 Rose Hill, Pleasantville
2	Post Office, Town Square, Pleasantville

id	person_id	address_id
0	0	0
1	1	1
2	2	0
3	0	2
4	1	2
5	2	2

Here each person may have many addresses and each address may be associated with many people, hence the many to many moniker.

Again the database and Pylons makes this operation quick and easy.

The intermediate table is sometimes known as the join table
The foreign key columns are normally named in a similar fashion to the many to one table
As before, we no longer need the address column from the original table

Further learning

Some basic SQL can be very useful for debugging purposes etc. Beware that SQL is slightly different for different database software. Also keep an eye out for database management software (such as CocoaMySQL) which will save you even having to learn this basic SQL.

Basic SQL 'GRANT' syntax – User management is not dealt with Pylons and for databases such as MySQL its a good idea to learn how to protect your database from edited by anyone
Basic SQL 'SELECT' syntax – It is useful to know how to read information direct from your database to verify that the Pylons output is correct
Basic SQL 'INSERT syntax – It can be useful to be able to input data manually so you can worry about input via Pylons later.