Basic SQL Question - Data Type Choice

Question

Hello all,

I am currently creating a small table in Oracle and am unsure of which data type to choose for a particular column.

The column is labelled 'stay' and I would like it to contain only these values 'Short', 'Medium' and 'Long'. The end goal is to have these values in a drop down list within a form.

Am I right in picking a data type of 'char(6)' here?

Is it then possible to use Oracle form builder to make this into a list of values?

Thank you for your time.

Answer 1

There are basically 3 options:

• Some databases support the type Enum specifically for this situations. (not sure about Oracle)
• You can use a char(6)
• You can use an int and map these at application level to the appropriate type.

I would say Enum is the 'cleanest' option. But some ORM don't support this properly.

This SO question might be interesting for you:

http://stackoverflow.com/questions/203469/how-to-use-enums-in-oracle

Answer 2

First of all, I would name the column 'StayDuration' or 'StayLength', as just 'Stay' by itself is still a little confusing. Once that is done, I see two acceptable approaches:

• Put your lengths in a separate table and have your existing column store a foreign key into that table.
• Store an integer, such that the value of the integer can be easily expanded into actual values for the stay length barriers via a simple formula. (Example: a 'short' stay could be 1 day or a less, 'medium' one week or less, and then everything else is long, you might use 0, 1, and 7 for your integers.

I tend to avoid storing varchar-type data directly here. It requires more storage and makes it easy to end up with mis-typed or obsolete data.

A hybrid approach is also possible, such that the 'keys' from the new table in the first option map to the integer values proposed in the 2nd.

Answer 3

Use VARCHAR2(6). CHAR(6) has blank-padded semantics, which is weird to use; it also means you store a full 6 characters for the word 'Long' every time you use it. There is almost never a compelling reason to use CHAR instead of VARCHAR2.

Answer 4

Use a CHAR(1) with values 'S', 'M', 'L'; optionally link the column to a second table where S/M/L are the primary keys, and "small/medium/long" are varchar(255) explanations. That way, you have optimal code size (1 byte is as short as possible), while still being able to print "nice" names in a report.

Answer 5

If there is an ENUM type available, go with that. Otherwise, I suggest two options:

• Store it as a single byte (or the smallest integral type available). Then you can convert to and from an enum in your application at will. I think this is the cleanest approach and binds quite well to ComboBoxes as a key-value pair.

• You can also store the strings in a separate table and have the existing column be a foreign key into this new table. It's then easy to JOIN on this table and get a fast string lookup to display on your form. However, it is additional overhead and possibly puts your strings in two locations - your application and the database. Moreover, if you ever want to localize, you're in for a real hassle.

---

I can't recommend going with the string (VARCHAR / CHAR) option if you want to build any application logic around this column. You'll be stuck comparing strings all day, which IMHO is rather ugly and slow. They're also slower to sort and query on than a simple integral type. (You are safe in your example, however, because you can just compare the first character 'S', 'M', or 'L' - a single byte. But you'll have to specifically instruct it to sort and query that way.)

If you simply are looking to display the value, you can go with the string, but keep in mind the size. Your database can easily blow up in size by storing the strings:

• 1,000,000 rows of a CHAR(6) is 5,000,000 bytes more than using a single byte.
• Using VARCHAR(6) would yield an average length of 5 = 4,000,000 bytes extra for 1,000,000 rows.

Answer 6

Using a numeric datatype makes it much easier to sort things by size if that's ever something you want to do. I can't speak for Oracle, but lots of times, using a single byte doesn't save you much, since it's going to get shoved into a 4 byte word anyway.

Also, you only have three sizes now, but you may well have many more in the future. Using a single character can make distinguishing between the XL and XXL size a real challenge.

Depending on the sort of application you're working on, storing strings presented to the user in the database can have implications down the road if you ever need to localize your application. If you're shipping the whole DB as part of the app, it's no big deal as long as you have them in a lookup table. If you're dealing with, say, a web application that needs to have different users accessing the same database in different languages, it can cause some problems.