To Domain or not to Domain

Question

The SQL92 and SQL99 standards define CREATE DOMAIN DDL constructs. Not all databases support this, or have a different name for it (SQL Server has User Defined Types, for example).

These allow one to define a constrained data-type to be used in their database, to simplify and enforce rules pertaining to the allowed values. Such a data-type could be used in column declarations, in inputs and outputs to stored procedures and functions etc...

I would like to know:

• Do people actually use domains in their database designs?
• If so to what extent?
• How useful are they?
• What pitfalls have you encountered?

I am trying to asses the viability of using these in future database development.

Answer 1

As usual...

It Depends

Do you have a domain entity used in more than one place that supporting natively would otherwise require considerable effort and/or redundant constraints and behaviors?

If so, domain away. If not, don't bother.

I've found it necessary to create a user-defined type in SQL Server exactly once, based on the above heuristic. I don't even remember what it was any more - but it saved more work than it caused.

Answer 2

As a user of SQL Server and C#, I haven't used User Defined Types in database, since I'm pretty more powerful in application side. Event after ORMs like LINQ to SQL and Entity Framework, my use of server capabilities has reduced a lot.

For example, I was using CLR integration to load some DateTime conversion functions to SQL Server to transfer Gregorian date-times into other formats, based on the Globalization power of .NET. But now, things are different, and I don't do that anymore. I simply load the data, and do the transformation, right in my application layer.

So, after almost 4 years of programming and inspecting all the teams I can think of, I've found no sample of using User Defined Types. I also haven't seen it in action in many .NET blogs.

While this doesn't mean that people don't use Database Domain, it surely means that at least using Database Domain is not that common.

Answer 3

There are already a detailed answer on Stack Overflow. I mostly agree with it, but not with the given example, I quote:

“For example, I define a "GenderType" (char(1), not nullable, holding "M" or "F") that ensures that only appropriate data is permitted in the Gender field.”

Personally, I feel more comfortable setting char(1) type and defining a constraint on the column. When the constraint is violated, I know exactly where to search to find what I did wrong. It's also more known than user defined types, so the database which uses only constraints would be easier to understand for a beginner.

Of course, just is just a personal opinion. Other people would say that an in ('M', 'F') constraint is not self-documenting and may be very obscure for a fresh developer who discovers the database.

IMO, use user defined types for more complicated types, and constraints for something basic. Also, when you cannot find easily an explicit name for a type, there is a chance that a constraint will fit better.

Answer 4

I have not used this feature myself but I guess you need to make sure that:

1. If your database is used by a single system, it may make sense not to use this feature and add the check logic in your business layer or its equivalent. That will give you more flexibility in validation (say in using regular expressions) and will make it possible to encapsulate all the validation logic in one place. If your database is shared by several systems, you may instead use triggers which are well suited for this task.

2. I am not sure if UDT allows you to customize the error messages returned to the client when a validation error is encountered.

3. UDTs are very useful if the same validation rule applies to several columns. In my opinion, I don't see this as a very common case in business application with normalized database design.

You may be interested in checking "What's the Point of [SQL Server] User-Defined Types?" blog article.

Answer 5

When you say "not all databases support this", I think a better way of putting it is the following :

Every major database supports this, as they support triggers, functions and other advanced features extensively.

This brings us to the conclusion that this is part of advanced SQL and makes sense at some point.

Do people actually use domains in their database designs?

The less possible, due to the extensive coverage required (considering operators, indexes, etc.)

If so to what extent?

Again, as limited as can be, if an existing type combined with a little additional defined logic (i.e. checks etc.) can do the trick, why go that far ?

How useful are they?

A whole lot. Let's consider for one second a not-so-good DBMS like MySQL, which I picked for this example for one reason : it lacks good support for the inet (IP address) type.

Now you want to write an application that is mostly focused on IP data like ranges and all that, and you're stuck with the default type and its limited functionality, you will either write additional functions and operators (like those supported natively in postgreSQL for example) or write much more complex queries for every functionality you need.

This is a case where you will easily justify the time spent on defining your own functions (inet >> inet in PostgreSQL : range contained in range operator).

At that point, you have already justified extending datatype support, there is only one other step to defining a new datatype.

Now back to PostgreSQL which has real nice type support but no unsigned int .. which you need, because you're really concerned about storage / performance (who knows ...), well you'll need to add it as well as the operators - although of course this is mostly deriving existing int operators.

What pitfalls have you encountered?

I don't play around with that as so far I haven't had a project that both required and justified the time required for this.

The biggest issues I can see coming with that would be reinventing the wheel, introducing bugs in the "safe" layer (db), incomplete type support which you'll only realize months later when your CONCAT(cast * AS varchar) fails because you did not define a cast(newtype as varchar), etc.

There are answers talking about "uncommon" etc. Definitely these are and should be uncommon (otherwise it means the dbms lacks a lot of important types), but on the other hand, one should remember that a (good) db is ACID compliant (unlike an application) and that anything related to consistency is better kept in there.

There are many cases where business logic is handled in the software layer and it could be done in SQL, where it's safer. App developers tend to feel more comfortable within the application layer and often avoid better solutions implemented in SQL, this should not be considered as good practice.

UDT's can be a good solution for optimization, a good example is given in another answer about the m/f type using char(1). If it had been a UDT it could be a boolean instead (unless we want to offer the third and fourth options). Of course we all know this isn't really optimization because of the column overhead, but the possibility is there.

Answer 6

On paper, UDTs are a great concept. They allow you to truly normalise your DB (e.g. when you have two columns that depend on each other), and also to encapsulate any logic related to your new type.

In practice, the price you pay (today) is too high. Obviously there is the development overhead, but aside from that you lose support from a variety of ORM and reporting tools, and increase the overall complexity of the solution quite a bit. There aren't too many developers out there who are intimately familiar with UDTs, and I've never seen managed UDTs used outside of examples.

One of the best examples of a type that is best done as a UDT (if it didn't already exist) would have to be hierarchyid (MSDN link). In order to remain efficient, it stores its value in a binary format (as opposed to the usual varchar custom implementations), which would be cumbersome to maintain without the type's functions. The 10 or so methods that the type provides are also best provided as part of the type, as opposed to external functions. I would consider using custom managed UDT in cases such as this - where there is a significant gain to be had by providing an efficient and neat implementation as a separate type.

Answer 7

A more practical question/answer. Does your company allows to use it ?

Its your company working with several D.B. S.Q.L. brands that handle different DDL (s) ?

Each database brand may offer good additional features, like User Defined Types, but, if your company uses several D.B., you may have trouble with the non standard features.

If the company its only you, or you can decide which Databases & features are you going to use, then you may use D.D.L.

I have work with several projects where a User Defined Type could be useful, but, I should stick to more standard features, since we had to deal with several DB. (s).