Ruby on Rails provides a simple mechanism to access a relational database. Data accessed on servers is usually kept in a relational database (RDB). SQL (Structured Query Language) is a standard interactive and programming language for getting information from and updating a relational database. Queries take the form of a command language that lets you select, insert, update, find out the location of data, and so forth. A relational database is a set of tables of rows and columns, each row having a unique key of one or more columns. A simple example of a relational database for a store:
Tables:
Customers
Sales representatives
Orders
Order lines
The organizing principle for an RDB is to keep related information in one table, and never duplicate data.
The customer table has the columns of data about customers:
Customer ID
Last name
First name
Balance
Credit limit
Sales representative number
Each row of the table represents one customer. Each row is uniquely identified by a key, in this case, the customer ID.
The Salesrep table has the data about sales representatives:
Salerep number
Last name
Fist name
Commission
Rate
There are relations (connections) between the tables. Information about the salesrep for a customer is found by getting the salerep number from the customer table, then selecting that salerep row in the salerep table.
The Orders table has data about customer orders:
Order ID
Order data
Customer ID
The Orderlines table has data about the parts that compose an order:
Order ID
Part
Quantity
Price
Note the relations between the tables. The Orders table connects the customer table (via customer ID) to the Orderlines table (via Order ID).
The data in the tables can be seen here.
Note that the salesrep name for each customer could have been kept as a column in the customer table, but this would require repeating the names many times. This is a violation of the rule to keep data in one place and not repeat data. This is done for simplification of adding/changing/deleting data. If each data item is kept in only one place, it is easy to change.
Using a relational database, you can extract information via an SQL query. The format of an SQL query to access data from an RDB:
Select columns to be selected
From tables that are needed for the query
Where conditions to select what rows will participate in the query
For example, to obtain the last name of all the customers who have salerep 03:
select last_name
from customer
where sales_rep = 03;
Note: When using mysql, you can type the query on one line. It is shown on multiple lines for clarity.
This is a simple query. Using SQL, no matter how complex the interrelationship of the data is, you can extract the information that you desire. Of course there is also a similar procedure to add/change data in the RDB.
The relations are used when the data you desire involves multiple tables. To display the last name of the salesrep for customer Galvez:
select salesreps.last_name
from customers,salesreps
where customers.last_name="Galvez" and customers.sales_rep=salesreps.id;
Logically this query proceeds like this:
Find the row in the customers table where last_name=”Galvez”. For the salesrep ID for this customer, find the row in the salesreps table where the ID matches this ID. Display the salesrep last name for this ID.
When multiple tables participate in a query, they are listed in the “from” part of the query. Multiple tables may have the same column name. To ensure the right colum is selected, it is preceded by the table name:
salesreps.last_name
To select parts for all orders for customer 522:
select orderlines.part
from customers,orderlines,orders
where customers.last_name=”Nelson” and customers.id=orders.customer_ID and orders.id=orderlines.id;
Three tables participate in ths query (the “from” part of the query). In the “where” part, the “customers.id=522” selects that row from the customers table. The “customers.id=orders.customer_ID” connects the customers table to the orders table by customer ID. The “orders.id=orderlines.id” connects the orders table to the orderliness table by the order ID. Logically this query proceed like this:
Look up customer last name“Nelson” in the customers table. Use the customer ID to find all orders for this customer in the orders table. Search the orderlines table for these orders and display all parts for these orders.