Wednesday, February 1, 2012

Postgresql - Domains

I'm studying good database design principles and discovered a beautiful concept : domains.

You have a SQL database and you want a column to be restrained to one of a few values.

If the list will never change, and ordering is preset or doesn't matter: use an ENUM
If the list might change but changes require programming, OR the list will not change but needs to be alpha sorted: use a DOMAIN
If the list will change, and changes do not require other programming: use a lookup table.


http://nigel.mcnie.name/blog/the-five-minute-guide-to-postgresql-domains-enums-on-steroids


For instance, I defined the following domains to enforce data validation to some extent.

CREATE DOMAIN general_text TEXT
 CONSTRAINT max_length CHECK (LENGTH( VALUE ) < 255 )
 ;

CREATE DOMAIN email_string TEXT
 CONSTRAINT valid_email_form CHECK ( VALUE ~ '_*@_*._*')
 CONSTRAINT valid_email_length CHECK ( LENGTH(VALUE) > 5)
 ;
 
CREATE DOMAIN number_string TEXT
 CONSTRAINT valid_number CHECK (VALUE ~ '[0-9]+')
 ;
 
CREATE DOMAIN telephone_string TEXT
 CONSTRAINT valid_telephone_form CHECK (VALUE ~ '[0-9\-]+')
 CONSTRAINT valid_telephone_length CHECK (LENGTH(VALUE) >= 8 )
 ;

CREATE DOMAIN counter int
 CONSTRAINT positive_number CHECK ( VALUE >= 0)
 ;

Defining a table that uses them:

CREATE TABLE editoriales(
  id serial primary key,
  nombre general_text not null,
  direccion general_text,
  estado_id int,
  pais_id int,
  codigo_postal number_string , 
  telefono telephone_string
);


The thing that got me into their use, is that they can be treated as if they were tables ,you can modify them / add new constraints and ... they are reusable!!!

No comments:

Post a Comment