We never store enumerations as numerical ordinal values anymore; it makes debugging and support way too difficult. We store the actual enumeration value converted to string:

public enum Suit { Spade, Heart, Diamond, Club }Suit theSuit = Suit.Heart;szQuery = "INSERT INTO Customers (Name, Suit) " +          "VALUES ('Ian Boyd', %s)".format(theSuit.name());

and then read back with:

Suit theSuit = Suit.valueOf(reader["Suit"]);

The problem was in the past staring at Enterprise Manager and trying to decipher:

Name                Suit==================  ==========Shelby Jackson      2Ian Boyd            1

verses

Name                Suit==================  ==========Shelby Jackson      DiamondIan Boyd            Heart

the latter is much easier. The former required getting at the source code and finding the numerical values that were assigned to the enumeration members.

Yes it takes more space, but the enumeration member names are short, and hard drives are cheap, and it is much more worth it to help when you're having a problem.

Additionally, if you use numerical values, you are tied to them. You cannot nicely insert or rearrange the members without having to force the old numerical values. For example, changing the Suit enumeration to:

public enum Suit { Unknown, Heart, Club, Diamond, Spade }

would have to become :

public enum Suit {       Unknown = 4,      Heart = 1,      Club = 3,      Diamond = 2,      Spade = 0 }

in order to maintain the legacy numerical values stored in the database.

How to sort them in the database

The question comes up: lets say i wanted to order the values. Some people may want to sort them by the enum's ordinal value. Of course, ordering the cards by the numerical value of the enumeration is meaningless:

SELECT Suit FROM CardsORDER BY SuitID; --where SuitID is integer value(4,1,3,2,0)Suit------SpadeHeartDiamondClubUnknown

That's not the order we want - we want them in enumeration order:

SELECT Suit FROM CardsORDER BY CASE SuitID OF    WHEN 4 THEN 0 --Unknown first    WHEN 1 THEN 1 --Heart    WHEN 3 THEN 2 --Club    WHEN 2 THEN 3 --Diamond    WHEN 0 THEN 4 --Spade    ELSE 999 END

The same work that is required if you save integer values is required if you save strings:

SELECT Suit FROM CardsORDER BY Suit; --where Suit is an enum nameSuit-------ClubDiamondHeartSpadeUnknown

But that's not the order we want - we want them in enumeration order:

SELECT Suit FROM CardsORDER BY CASE Suit OF    WHEN 'Unknown' THEN 0    WHEN 'Heart'   THEN 1    WHEN 'Club'    THEN 2    WHEN 'Diamond' THEN 3    WHEN 'Space'   THEN 4    ELSE 999 END

My opinion is that this kind of ranking belongs in the user interface. If you are sorting items based on their enumeration value: you're doing something wrong.

But if you wanted to really do that, i would create a Suits dimension table:

This way, when you want to change your cards to use Kissing Kings New Deck Order you can change it for display purposes without throwing away all your data:

Now we are separating an internal programming detail (enumeration name, enumeration value) with a display setting meant for users:

SELECT Cards.Suit FROM Cards   INNER JOIN Suits ON Cards.Suit = Suits.SuitORDER BY Suits.Rank,    Card.Rank*Suits.CardOrder    

Unless you have specific performance reasons to avoid it, I would recommend using a separate table for the enumeration. Use foreign key integrity unless the extra lookup really kills you.

Suits table:

suit_id suit_name1       Clubs2       Hearts3       Spades4       Diamonds

Players table

player_name suit_idIan Boyd           4Shelby Lake        2

1. If you ever refactor your enumeration to be classes with behavior (such as priority), your database already models it correctly
2. Your DBA is happy because your schema is normalized (storing a single integer per player, instead of an entire string, which may or may not have typos).
3. Your database values (suit_id) are independent from your enumeration value, which helps you work on the data from other languages as well.

As you say, ordinal is a bit risky. Consider for example:

public enum Boolean {    TRUE, FALSE}public class BooleanTest {    @Test    public void testEnum() {        assertEquals(0, Boolean.TRUE.ordinal());        assertEquals(1, Boolean.FALSE.ordinal());    }}

If you stored this as ordinals, you might have rows like:

> SELECT STATEMENT, TRUTH FROM CALL_MY_BLUFF"Alice is a boy"      1"Graham is a boy"     0

But what happens if you updated Boolean?

public enum Boolean {    TRUE, FILE_NOT_FOUND, FALSE}

This means all your lies will become misinterpreted as 'file-not-found'

Better to just use a string representation