The primary feature of relational database systems (such as MySQL) is the ability to link data to other bits of data using relationships. Consider a database that stores states and cities in the United States. Each state can have many cities, but each city belongs to only one state. These relationships, "has many" and "belongs to", are definable using concrete5's active record support.
In this recipe, we will link two tables, States
and Cities
, and use the relationships to retrieve the related data.
First, we will need to create tables for both Cities
and States
. Use the following SQL to populate those tables (this SQL is also available for download from the book's website):
CREATE TABLE `Cities` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT '', `state_id` int(11) NOT NULL, PRIMARY KEY (`id`) ); INSERT INTO `Cities` (`id`, `name`, `state_id`) VALUES (1,'New York',2), (2,'Buffalo',2), ...