Using Domains to Enforce Business Rules     

Then to use this new domain, you can create a table of the form :

CREATE TABLE payroll(pid serial primary key, employee_id varchar(20), payamt numeric(10,2), dtpaythru dom_payday);  

Now if someone tries to stuff a day that doesn't fall on a Friday like for example:

INSERT INTO payroll(employee_id, payamt, dtpaythru) VALUES('robe', 10000, '2011-04-30');

Domain Features: Changeable and Castable

The other two nice things I like about domains or at least how PostgreSQL implements them is:

• They can be changed. The main caveat is that all your data needs to still abide by the constraints of the revised domain. If it doesn't, PostgreSQL will throw an error of the form: column "dtpaythru" of table "payroll" contains values that violate the new constraint detailing the table and column in violation. The downside of this checking is that for large tables it could take a while.

  More details about altering domains can be found at http://www.postgresql.org/docs/current/interactive/sql-alterdomain.html.

  Let's say before April 1st 2011 our pay day was every Friday, but after April 1st 2011, we changed the rule so that we paid every Saturday. We would alter domain like so:

  ALTER DOMAIN dom_payday 
      DROP CONSTRAINT check_dow;
  ALTER DOMAIN dom_payday    
     ADD CONSTRAINT 
          check_dow CHECK ( 
          -- before April payday was friday
              (VALUE < '2011-04-01'::date AND trim(to_char(VALUE, 'day')) = 'friday') 
                  OR 
         -- after April 2011 pay day was saturday
              (VALUE >= '2011-04-01'::date AND trim(to_char(VALUE, 'day')) = 'saturday' ) 
        );
  

  After we make this change then the aforementioned insert will work.

• Perhaps a bit of a hack, but you can use them as portable rules by using the fact that just like data types, you can cast to them. This is not something I have tried in other systems that support them, but I imagine it might be a feature unique to PostgreSQL because of its very dynamic casting architecture. So to check if a date is a vaild pay day, I can do this test and if it successfully casts, it conforms to the payday rule, and if it throws an error, it doesn't.

  SELECT '2010-04-30'::dom_payday;
• Domains (at least in Postgres) can have multiple check constraint rules attached to them though in most cases, people just attach one. You can add more rules to a domain by using the
  ALTER DOMAIN .. ADD CONSTRAINT

Now domains are essentially a packaging of atomic rules. You can't use them to search across other related columns of a table so their utility is somewhat limited.

Domains to control validaty of rule expressions, multi-constraint domains

In one of our new ventures -- Arrival 3D we are constructing 3D platforms (we call the core SimAisle) to manage real world assets and also to integrate with other asset management systems lacking a strong on the ground perspective. We are using many of the unique features only available in PostgreSQL. I've had some qualms about that just because I like my code to have the furthest reach. When the database is essentially a big core of your platform, which it is in this case, you have to make concessions of time to make something portable to other databases vs. the cost of implementation. In this case, so many things are easier to do in the PostgreSQL that the cost in savings mitigates the effort to make it work on other databases. So with that said -- we achieved our primary objectives of OS agnostic (PHP, PostgreSQL), more or less standards compliant (X3D, OGC SFSQL, SQL), integration with other systems and databases, but failed on the DB Agnostic side. That is not to say just because PostgreSQL has a cool feature such as arrays should you use it because it's there, but by all means if the shoe fits more comfortably than other shoes, wear it. We are really excited about the upcoming SQL/MED features in 9.1 since it will even more simplify our database integration functionality.

One of the things we struggled with was how to best control connector points of an object. In our model each object type can have up to 28 connector points, but not all are valid. So there is the rule to define how the connector point rule should be expressed. We thought about storing the rule as an array because its essentially a sequence of integers, but we decided that would be messy to handle in all the places we cared and that whenever we injected it into our X3D scene, we'd need it to be a space delimited list to conform with the X3D formatting of arrays. That said we just store as string but ensure the string represents a valid connector point rule. So we use domains to enforce that with a domain that looks something like:

CREATE DOMAIN dom_connector_positions
  AS text
   CONSTRAINT check_within_range 
        CHECK (((0 <= ALL ((string_to_array(VALUE, ' '))::integer[])) AND (27 >= ALL ((string_to_array(VALUE, ' '))::integer[])))) 
    
   CONSTRAINT check_space_delimited_2digit_ints 
        CHECK ((VALUE ~ '^([0-9]{1,2})( [0-9]{1,2})*$'));