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.
