We like to enforce business rules at the database level wherever
we can, for the simple reason, particularly the business we are in, most database update happens
outside the end-user application layer.
That is not to say you shouldn't enforce at the application level too, but that the database is the last
line of defense, is usually more self-documenting than application code can be, and also protects you from your
programmers, even when that your programmers is you.
Domains are objects that you will find in many high-end
standards-compliant databases. They exist in SQL Server, Oracle, IBM Db2, Firebird, and PostgreSQL to name a few.
Domains have existed for a really long time in PostgreSQL. In PostGIS topology, Sandro Santilli (usually known as strk), takes advantage of them for fleshing out the topology support, and I got turned on to them by him.
With that said - let's dive into domains.
What are domains?
Domains are essentially a reusable packaging of check constraints. You use them as if they were a custom data type.
The nice thing about them is that they are usually transparent to applications that
don't understand them.
Example 1: Enforce pay ending/pay day happens only on certain days of the week
Here is an example -- suppose you had a payment system, and you had a rule that the pay thru end date has to
fall on a Friday. You could create a domain such as the following:
--paydaydomainCREATE DOMAIN dom_payday
CONSTRAINT check_dow CHECK(trim(to_char(VALUE, 'day'))='friday');
COMMENT ON DOMAIN dom_payday IS'Companypaydayrules';
We just got our complimentary author hard-copies of PostGIS in Action today. Those who ordered directly from Manning should be getting there's shortly too if they haven't already. Amazon and other distributors should start shipping soon as well.
We'll be saving some copies for door prizes at the next event we present at.