For example: I want a data type called season (= { spring, summer, fall, winter}
views:
78answers:
1
+5
A:
Define a
seasontable, having two columns:season_id, andseason_name:CREATE TABLE "SEASON" ( "SEASON_ID" NUMBER NOT NULL ENABLE, "SEASON_NAME" VARCHAR2(6 BYTE) NOT NULL ENABLE, CONSTRAINT "SEASON_PK" PRIMARY KEY ("SEASON_ID") )Add a
season_idcolumn to table(s) whose records need season identification, and create a foreign key relationship to theseasontable
CHECK constraints are the next option, but suck if you want to use the values in more than one table because they can't be centrally managed. Triggers are also an option, but in this case triggers are more like a cannon to kill a mosquito compared to a CHECK constraint...
Which is best?
To quote sql_mommy:
The [separate table, with a foreign key relationship,] is the best.
It is a single place for managing the desired type, and it's flexible so you can add/subtract values or edit as needed. It makes for good indexing, too. And in case you might think "but the contents of this enum will never need to be edited" - in my experience ALL data types eventually morph. For example, if you have a list of months, later you might add various abbreviations of months or business related associations with those months. Using the relational structure of the database provides the most flexibility with the easiest maintainability.
OMG Ponies
2010-09-19 19:18:25
Thx, is this the best way? what about using constraints?
baboonWorksFine
2010-09-19 19:22:44
This is the best way. It is a single place for managing the desired type, and it's flexible so you can add/subtract values or edit as needed. It makes for good indexing, too. And in case you might think "but the contents of this enum will never need to be edited" - in my experience ALL data types eventually morph. For example, if you have a list of months, later you might add various abbreviations of months or business related associations with those months. Using the relational structure of the database provides the most flexibility with the easiest maintainability.
sql_mommy
2010-09-19 19:33:04
@sql_mommy: Well said, hope you don't mind that I quote you.
OMG Ponies
2010-09-19 19:35:57
np - glad it was helpful!
sql_mommy
2010-09-19 21:18:40
The flip side of that is that if a domain is truly, truly static, the code path to validate a check constraint is significantly less than the code path to validate a referential integrity constraint. How many folks maintain a BOOLEAN_TYPE table, and tie all the true/false columns to it?
Adam Musch
2010-09-20 03:01:10
+1, but I wouldn't be so dogmatic about it. Most systems I've created or maintained have a mixture of columns that are simple and static, and a CHECK constraint is suitable; and other columns which need a FK to a static data table. Neither is "the best way". Plus, it's trivial to refactor a CHECK constraint into a FK table if needed.
Jeffrey Kemp
2010-09-20 05:04:46
Plus, and this is more important: if the valid values for the column are used in the logic of the application, an advantage of using a CHECK constraint is that you are effectively declaring: this column MUST NOT have any other values - otherwise the existing codebase may break! In other words, with a CHECK constraint, I'm allowed to write code like "`IF mycolumn = 'FOO' THEN ... ELSE /*mycolumn is 'BAR'*/ ... END IF`" - and I know this can't break. Whereas if the values are defined by data, I have to code some kind of error routine if an unexpected value is returned.
Jeffrey Kemp
2010-09-20 05:08:26
SEASON is a good candidate for an index organized table, too.
Thilo
2010-09-20 06:29:08
You never know when you need an extra season.
Rene
2010-09-20 07:17:31