Dataface applications without relationships between tables can be quite boring.  In our FacultyOfWidgetry application, we have implicitly defined a relationship between the Program table and the Course table by adding a ProgramID field to the Course table.  In the previous section we saw how to configure this relationship from the context of a 'Course' by adding a select list to the Course table form to select the Program that the Course belongs to.

From the 'Program' side it is a little bit more complicated.  There are no fields in the 'Program' table that can be edited to add a 'Course' to the list of courses in a 'Program', and it would be highly inconvenient to have to edit a 'Course' record in order to add the course to a 'Program'.  What we want is a sort of 'Add Course' button to add a course to a 'Program'.

Concepts, Definitions, and Terminology

Before we can delve into examples, it will help to go over some of the concepts and terminology involved in relationships using Dataface.  Dataface relationships are always defined in a one-way fashion from the point of view of one table.  For example, if you define a one-to-many relationship from the 'Program' table to the 'Course' table, the mirror (many-to-one) relationship from 'Course' to 'Program' is not automatically created.  This method of defining relationships allows us to unambiguously refer to the source and destination tables of a relationship.

Definition 1: The source table of a relationship is the table on which a relationship is defined.  For example if we define a relationship named 'Courses' on the 'Program' table to associate courses in a given program, then the 'Program' table would be considered the source table of the relationship, and the 'Course' table would be a destination table of the relationship.

Definition 2: The destination table of a relationship a table from which related records are selected.

There may be multiple destination tables in a given relationship but only one source table.  If there are multiple destination tables, then one of these tables is designated as the domain table and the remaining destination tables are called join tables.

Definition 3: A domain table is a destination table which stores the object of the relationship.  For example if we define a many-to-many relationship between the 'Program' table and the 'Course' table, (i.e., each program can contain multiple courses and each course can be part of multiple programs), then we would need to add a join  table to map 'Course' records to 'Program' records.  Let's call this table 'ProgramCourses'.  Each record of the 'ProgramCourses' table would contain a 2 fields: a 'ProgramID' field (to reference the program) and a 'CourseID' field to reference the course.  If we define the relationship from the point of view of a 'Program' then the 'Program' table would be the source table, the 'ProgramCourses' table would be the join table, and the 'Course' table would be the domain table.

Don't worry if these definitions and terms aren't clear at this point.  Use this section as a reference for when you run across the terms later in the tutorial.

Defining a relationship

To define a relationship in Dataface, all you need to do is tell dataface how to select the related records using SQL.  Dataface will be able to figure out how to add/remove records to the relationship from this information.  This information is defined inside a the 'relationships.ini' file inside the configuration folder for the table.

Example 1: Adding a 'Courses' relationship to the 'Program' table

We want to be able to associate multiple courses with each program.  We do this by defining a relationship on the 'Program' table as follows:

1. Add a file named 'relationships.ini' to the 'Program' table's configuration folder (i.e., tables/Program/relationships.ini).  Your application's directory structure should now look like:
   
   

   Notice, in particular the addition of the 'relationships.ini' file in the 'Program' directory.

2. Add the following to the 'relationships.ini' file:
   

   [Courses]
   Course.ProgramID = "$ProgramID"
   

   This little snippet defines a relationship named 'Courses' on the 'Program' table.  'Program' is the source table.  'Course' is the destination table.  There are no join tables because this is only a one-to-many relationship.  You may be wondering what the $ProgramID means.  This is a variable that represents the value of the 'ProgramID' field in the source record.  This relationship specifies that courses whose 'ProgramID' field matches the value of the 'ProgramID' field in the source record, are related to the source record. The english language makes this seem more difficult and complex than it really is.

Let's check out our changes.  Since we have defined the relationship on the 'Program' table, we will click on the 'Program' link in the navigation menu:

Notice that there is now a 'course' tab at the top of the page.  Click on this tab to see the courses that are related to this Program (as defined by our 'Courses' relationship:

Currently there is only one course in the program, but we can add more.  If you click on the "Add New Courses" button in the upper left, you will be presented with a new course form that will allow you to add a new course in this Program.

Example 2: Making the 'Courses' relationship a Many-to-Many relationship

Example 1 shows how Dataface can handle a one-to-many relationship.  Now we will alter the database a little bit and turn this into a many-to-many relationship.

1. Add a table named 'ProgramCourses' to the database.  The SQL table definition for this table should be something like:
   

   CREATE  TABLE  `ProgramCourses` (
       `ProgramID` INT( 11  )  NOT  NULL ,
       `CourseID` INT( 11  )  NOT  NULL ,
       PRIMARY  KEY (  `ProgramID` ,  `CourseID`  ) 
   ) 
   
   

   Note that it is important for ALL of your tables to have Primary keys.  If a table is missing its primary keys, some strange behavior may occur with relationships involving that table.
   

   The above defined table will serve as a join table between 'Program' and 'Course'
   

2. Since this is now going to be a many-to-many relationship, we no longer need the 'ProgramID' field in the 'Course' table. (Do not confuse this with the ProgramID field in the 'Program' table.  That field is important and needed.).  Before removing this field, we will transfer the information across so that the existing relationships are maintained.  The following SQL query will effectively all of the old one-to-many relationships into equivalent many-to-many relationships:
   

   INSERT  INTO ProgramCourses( ProgramID, CourseID ) 
       SELECT ProgramID, CourseID
       FROM Course
   

   And now we can remove the 'ProgramID' field from the 'Course' table.
   

   ALTER  TABLE  Course  DROP  ProgramID

3. Finally, we will need to modify the relationship definition in the relationships.ini file of the 'Program' table:
   

   [Courses]
   Course.CourseID = ProgramCourses.CourseID
   ProgramCourses.ProgramID = "$ProgramID" 
   

   This means that all courses for which a (ProgramID, CourseID) pair matches the CourseID of the course and the ProgramID of the source Program record are included in the relationship.

4. Now we can check our application for changes.  Go to the 'Program' table in your application (using your web browser) and click on the 'courses' tab once again:
   
   

   This looks almost the same as before.  Notice, however that now there is an "Add Existing Courses Record" button at the top.  This is because with a many-to-many relationship, you are able to add related records in 2 ways:
   

   • Adding a completely new record that did not exist before.
   • Selecting a record that already exists and adding it to the relationship.

Example 3: Defining Relationships using SQL

The previous examples used a simple INI file syntax to define relationships.  However, some people may be more comfortable defining their relationships using SQL.  This is also possible.  Let's look at the relationships.ini file from example 1:

[Courses]
Course.ProgramID = "$ProgramID"

This also could have been defined as follows:

[Courses]
__sql__ = "SELECT * FROM Course WHERE ProgramID='$ProgramID'"

Note: Make sure you use two underscores on either side of 'sql' in the above example.  It should be '__sql__' not '_sql_'.

The two syntaxes are equivalent.  In fact, the former will be converted into the later by Dataface behind the scenes.

Now let's look at example 2's relationships.ini file:

[Courses]
Course.CourseID = ProgramCourses.CourseID
ProgramCourses.ProgramID = "$ProgramID" 

This could have been written as:

[Courses]
__sql__ = "SELECT * FROM ProgramCourses, Course WHERE Course.CourseID = ProgramCourses.CourseID AND ProgramCourses.ProgramID = '$ProgramID'"

The two are equivalent.  This example, however, shows how defining a relationship using SQL can be beneficial.  The above SQL query will work but it can be done better using Joins as follows:

[Courses]
__sql__ = "SELECT * FROM ProgramCourses pc INNER JOIN Course c ON pc.CourseID = c.CourseID WHERE pc.ProgramID = '$ProgramID'"

All of these 3 methods will produce the same results, but the last one will probably give a little bit better performance.

Relationship Restrictions

Dataface has built-in logic to figure out how to add new and existing records to relationships that you define, as long as your relationships obey a few guidelines.

1. All tables must have a Primary key
2. The WHERE clause of your SQL definition for the relationship must contain only '=' comparisons, and 'AND' conjunctions. i.e., it cannot receive an 'OR' conjunction, nor can comparisons be done using '>', or '<'.  This is because given 'AND' and '=' conjunctions it is easy for dataface to be able to add records that will satisfy the relationship.  If an 'OR' conjunction is used, it makes it ambiguous (though this will probably be corrected in future dataface releases.