What is the best way of define "ENUM" data type when creating a table in Oracle 10?

For example: I want a data type call开发者_如何学编程ed season (= { spring, summer, fall, winter}

---

1. Define a season table, having two columns: season_id, and season_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")
   )
   

2. Add a season_id column to table(s) whose records need season identification, and create a foreign key relationship to the season table

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.