Class Relations
Prev | Chapter 2. CommonSQL Tutorial | Next

## Class Relations

In an SQL only application, the EMPLOYEE and COMPANY tables can be queried to determine things like, "Who is Vladimir's manager?", "What company does Josef work for?", and "What employees work for Widgets Inc.". This is done by joining tables with an SQL query.

Who works for Widgets Inc.?

    SELECT first_name, last_name FROM employee, company
           WHERE employee.companyid = company.companyid
    	     AND company.company_name = "Widgets Inc."

Who is Vladimir's manager?

    SELECT managerid FROM employee
           WHERE employee.first_name = "Vladimir"
    	     AND employee.last_name = "Lenin"

What company does Josef work for?

    SELECT company_name FROM company, employee
           WHERE employee.first_name = "Josef"
    	     AND employee.last-name = "Stalin"
    	     AND employee.companyid = company.companyid

With CLSQL however we do not need to write out such queries because our view classes can maintain the relations between employees and companies, and employees to their managers for us. We can then access these relations like we would any other attribute of an employee or company object. In order to do this we define some join slots for our view classes.

What company does an employee work for? If we add the following slot definition to the employee class we can then ask for it's COMPANY slot and get the appropriate result.

        ;; In the employee slot list
        (company
          :accessor employee-company
          :db-kind :join
          :db-info (:join-class company
    	        :home-key companyid
    		:foreign-key companyid
    		:set nil))

Who are the employees of a given company? And who is the president of it? We add the following slot definition to the company view class and we can then ask for it's EMPLOYEES slot and get the right result.

          ;; In the company slot list
          (employees
    	:reader company-employees
    	:db-kind :join
    	:db-info (:join-class employee
    		  :home-key companyid
    		  :foreign-key companyid
    		  :set t))

           (president
            :reader president
    	:db-kind :join
    	:db-info (:join-class employee
    		  :home-key presidentid
    		  :foreign-key emplid
    		  :set nil))

And lastly, to define the relation between an employee and their manager:

    	;; In the employee slot list
           (manager
            :accessor employee-manager
    	:db-kind :join
    	:db-info (:join-class employee
    	          :home-key managerid
    		  :foreign-key emplid
    		  :set nil))

CLSQL join slots can represent one-to-one, one-to-many, and many-to-many relations. Above we only have one-to-one and one-to-many relations, later we will explain how to model many-to-many relations. First, let's go over the slot definitions and the available options.

In order for a slot to be a join, we must specify that it's :db-kind :join, as opposed to :base or :key. Once we do that, we still need to tell CLSQL how to create the join statements for the relation. This is what the :db-info option does. It is a list of keywords and values. The available keywords are:

- :join-class - The view class to which we want to join. It can be another view class, or the same view class as our object.

- :home-key - The slot(s) in the immediate object whose value will be compared to the foreign-key slot(s) in the join-class in order to join the two tables. It can be a single slot-name, or it can be a list of slot names.

- :foreign-key - The slot(s) in the join-class which will be compared to the value(s) of the home-key.

- :set - A boolean which if false, indicates that this is a one-to-one relation, only one object will be returned. If true, than this is a one-to-many relation, a list of objects will be returned when we ask for this slots value.

There are other :join-info options available in CLSQL, but we will save those till we get to the many-to-many relation examples.

### Object Oriented Class Relations

CLSQL provides an Object Oriented Data Definition Language, which provides a mapping from SQL tables to CLOS objects. By default class inheritance is handled by including all the columns from parent classes into the child class. This means your database schema becomes very much denormalized. The class option :normalizedp can be used to disable the default behaviour and have CLSQL normalize the database schemas of inherited classes.

See `def-view-class` for more information.

Prev | Up | Next
Data Modeling with CLSQL | Home | Object Creation