Introduction

This section willl cover relational databases. Just to orient ourselves, let's take a look at the web application architecture and tiers that we've previously defined, and the encircled area are the tier this section focusing on.

Typically, the encircled area involves a relational database as a part of this back end. More recently, there's something called NoSQL databases that have been created. Now, these databases that are relational databases, are typically SQL databases, stands for SQL, structured query language. The NoSQL databases have become very popular recently and they can also be made to work on a Rails back end.

Relational Databases

• Relational databases are the most common way to persistently store data in web applications.
• A relational database is used to store a collection of relations. This involves storing "records" in tables.
• Each row in a table (or entity) corresponds to one record, and the columns correspond to field (or attributes) of the record.
• Example:
  
  

• What's the point of the id field in above table? It is used to form relationships to other tables. It's referred to as the primary key of the table.
• For example, John Doe appeared in above table twice because he has two phones. More specifically, there is a one-to-many relationship between person and phones - one person can have many phones.
• We can normalize the databases by creating two tables, one for people and a separate table for phones:
• Each record in the phone table will hold the id of a person
• In the phone table, this person_id is referred to as a foreign key .
• Given the id of a person , we can now search the phone table for all of the phones that belongs to a person. This is typically done using structured query language (SQL), but in Rails, it can be by-pass this using the methods provided with Active Records.
• Example:
  
  

Schema and Entity-Relationship Models

The structure or organization of a database is often referred to as a schema. And we usually use entity-relationship models to model the relationship between the tables in a database.

Here's an example of an entity-relationship diagram for the two tables that just showed. (This can be created using MySQL Workbench tool)

A bit more about this: many-to-many relationship

• Notice that we could further normalize the database by creating an address table.
• However, in this case, the one-to-many relationship is in the other direction, i.e. we have one address for many people in the table.
• You can imagine a situation where one person also has many address, e.g. one for work, one for home, etc.
• Thus, we really need to create a many-to-many relationship between people and addresses.
• This is done by creating a join table - it is called this because it "joins" the people and addresses tables.
• The join table in the following example is called addresses_people. Notice that it only stores foreign keys, and has no primary keys.
• Example:
  
  
  
• The Entity-Relationship Model (This can be created using MySQL Workbench tool)