The Business Rules Engine

Step 2: Create Address Tables

Now that we have the application created, we can begin creating the tables and forms necessary to store the student data.

So what is the difference between a table and form? A table is very much like a SQL table; a data structure with pre-defined columns of particular types, in which data is stored in rows. A form is almost exactly the same, except that it has additional workflow defined for it. This workflow exists as different "form statuses" and the ability for the user to move rows between them using developer-defined verbs. For a more detailed discussion of how this works, please visit the forms reference section.

Whenever we decide we need to store a type of data in CodaServer, we must ask ourselves if that data would best be served by a table or form.

Let's look at Tina's requirements.

She needs to store a list of students along with their contact information. Let's assume for the time being that students will have two addresses, their home address and their school address, and that these addresses can change over time. How should we model this?

First, we will need a table to store addresses. Addresses are blocks of information that don't change a whole lot over time; in fact, as the student moves around, we can just create new address records and assign them to the students. Individual address records won't likely change at all. A table is the correct way to store these.

We can create the table like this:

CREATE TABLE addresses (
     address STRING NOT NULL, 
     address_cont STRING NULL, 
     city STRING NOT NULL, 
     state_prov STRING NOT NULL, 
     country STRING NOT NULL, 
     postal_code STRING NOT NULL

Note: Database purists might notice a lot wrong with this table definition, such as the rather liberal use of text blocks. As we work through the tutorial, we will keep coming back to this same schema and make it better with each iteration. Don't worry, we've got you covered!

Even in this simple example, we can see some differences between Coda tables and SQL tables. Coda tables don't require primary keys or create and modify audit columns; these are added to every table in the background without any user interaction. Also, SQL types are replaced by Coda's more liberal types.

Looking at your new table, you start to think that maybe the country column should be limited to only real countries. How can we do this? Make a table of countries, and make the country column reference it.

CREATE REF TABLE countries (
     country_code STRING NOT NULL, 
     country_name STRING NOT NULL

Now we have to change the country column in our first table:

ALTER TABLE addresses 
     ALTER COLUMN country SET
     country REFERENCE TO countries NOT NULL;

In the countries table, you decided to use the REF TABLE option. Good choice. Ref tables are identical to regular tables, except that they are replicated automatically across the development, testing, and production environments. Any rows added, updated or deleted in those environments immediately percolate to all of the others. This is perfect for data such as countries which generally don't change based on your application's whims.

The ALTER TABLE command in the second statement made use of the REFERENCE TO column type, which is CodaServer's implementation of SQL's foreign keys. This is but one way to state a relationship between two tables, a theme we will come back to several times as we build our application.