4

Following the spirit of this question, what should be the ideal way to store enums in a database in Delphi? I have several lookup tables that guide some business logic, so a) they are tightly coupled with my code, and b) they may be subject to change in newer releases.

Right now I'm doing it by saving its numeric values, and setting the enum value explicitly.

type TSuit = (Unknown = 4, Heart = 1, Club = 3, Diamond = 2, Spade = 0);

Do you know a better way?

Community
  • 1
  • 1
Leonardo Herrera
  • 8,388
  • 5
  • 36
  • 66
  • 3
    Store as integer is the best you can do. – David Heffernan Feb 04 '12 at 17:16
  • @DavidHeffernan Storing as strings and using GetEnumName/GetEnumValue is another way of doing it. Takes more space but avoids the problem of the integers getting a different meaning when inserting new values in an enum. (Not likely in this particular example, but in general it happens and should be possible. At least it shouldn't be avoided just for persistence reasons). – Marjan Venema Feb 04 '12 at 17:22
  • @Marjan If you store strings instead then you equally make it hard to change the names in the future. There's no getting round that issue. – David Heffernan Feb 04 '12 at 17:23
  • @DavidHeffernan: yes that's true. Though I'd rather have a problem arise because "prSomeName" is not a valid value for (when converting it back to an enum value), than have subtle bugs because 1 no longer corresponds to prSomeName but to prSomeOtherName... – Marjan Venema Feb 04 '12 at 17:27
  • 1
    @Marjan Venema - I can't see any advantage in storing the name. In the example above, the numbers are specified, so you can insert new values without any problems, you can even change the order and delete values. Storing strings on the other side could theoretical lead to problems with case sensitive comparisons. – martinstoeckli Feb 04 '12 at 21:07
  • This really depends on DBMS used. And BTW, your ordinal 4 is nonsense. – OnTheFly Feb 04 '12 at 22:13
  • @user539484 BTW, how would you store 'Unknown'? (I guess I would prefer 0 instead of 4 for Unknown, but 'null' values are real) – mjn Feb 04 '12 at 22:17
  • 1
    You might make another table and store enums as a foreign key. – Mihaela Feb 05 '12 at 03:23
  • @user539484 yes, NULL is a sensible database value for an "unknown" TSuit value. But while ordinal 4 is a valid Object Pascal expression in the enumeration, NULL is not. So there would be a mapping between the case 'Unknown TSuit' and 'NULL'in the database. – mjn Feb 05 '12 at 08:42
  • @martinstoeckli: most enums do not have specified numbers. Even when they do, the numbers are usually related to some algorithm in the program. When that changes, the meaning of a number can change, leading to "subtle" bugs if the values in the database aren't converted. Names usually are more resistent to change. Using names for values in the database is therefore more resistent to change and has the added benefit of coming up with an error if a value is retrieved that can be converted back into an enum value. – Marjan Venema Feb 05 '12 at 10:57
  • @martinstoeckli: GetEnumName uses CompareText so is case-**in**senstive. Case-sensitivity could therefore only be a problem when using SQL directly on such columns and if you haven't addressed SQL case-(in)sensitivity in a generic manner, you are in a pickle for all user-entered values, never mind values that are under strict programmatic control. – Marjan Venema Feb 05 '12 at 10:58

1 Answers1

3

Given the relational model you probably should store the value of the enumeration into the table at hand and create another table where the enum value + name are stored. The latter can be created purely from RTTI.

So given your example: you have something like a Card table where Suit is a byte field with values 0..4. And you have a Suits table with 5 records, one for each enum value.

Now your data is correctly normalized and the metadata is present you you know what Suit=2 means (eg join with the Suits table).

Ritsaert Hornstra
  • 5,013
  • 1
  • 33
  • 51