Options for replacing enums stored in database....

[Martin Pritchard]

Hi,

I'm working on a project that historically contains around 40 enums. In the
database various fields refer to the int values of these enums, but of
course ref integrity is not enofrced and when looking at the db we can't
tell what the value in a field represents. The other problem is that our
enums are currently all stored in a single class, which means that because
of no visibility constraints the enums are often used out of context e.g. A
CarEngine enum being used instead of a BikeEngine enum.

What I'd like to do is:

1) store the enums in the database so that we can enforce ref integrity. I
don't want to have to create 40 odd tables to store these values either. The
actual int value of the enum is unimportant as we won't be performing enum
addition (like CarEngine.4Cylinder + CarEngine.Petrol).
2) dynamically create several classes within individual projects to allow us
to restrict visibility of the enums to within a project.

I have considered using three tables to store them - one for the class, one
for the enum type and one for the enum value and then (somehow) dynamically
building the class at compile time, but I have no idea how to do this!
Also, it does leave the problem of still not enforcing the ref integrity
100% i.e. the CarEngine field could store a BikeEngine enum value and still
enforce basic referential integrity as they're both enum values, albeit of
different types. Is it possible to enforce ref integrity in such a way to
prevent this?

The values held in the enum will not be changed between code releases and so
do not need to be truly dynamic. I would rather not have to hard code in
strings in our code to refer to the database.

Any clues about how to go about this or any better ideas / nice URLs to look
at?

Hope you're still following me!

Martin

 

[Chris Priede]

Hi,

I'm working on a project that historically contains around 40 enums.

By this I presume you mean C# enum type.

In the database various fields refer to the int values of these enums, but
of course ref integrity is not enofrced

Two words -- "check constraints":

http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_04_77qr.asp

The basic check constraint validates values within a single row. Should you
ever require more elaborate validation logic (comparing to values from other
tables/rows, and so forth), you can even do that -- place it in a T-SQL user
defined function and refer to this function in the constraint.

when looking at the db we can't tell what the value in a field represents.

If that is a serious issue, store the enum value names as strings. It's ok.
Really. Slight (and usually insignificant) size/performance penalty
aside, the SQL database engine can do everything it does with strings just
as well as it can with ints. You can still use check constraints and you
can easily translate names to enums in your C# code with enum.Parse().

If it comes up rarely -- for example, every once in a while you are asked to
extract some sort of a one-time report where you could just run a SQL query,
but need the names -- put the int values and their corresponding names in a
table you can join to your query on those occasions.

The other problem is that our enums are currently all stored in a single
class, which means that because of no visibility constraints the enums are
often used out of context e.g. A CarEngine enum being used instead of a
BikeEngine enum.

Split them up. Also, adding proper check constraints to your database will
quickly put a stop to that.

 

[Martin Pritchard]

In the database various fields refer to the int values of these enums,

Two words -- "check constraints":

I know all about check constraints, and to get them to do what I want it
would require hard coding data (the enum type) into the check constraint.
Not exactly best practice is it? Not to mention check constraints will be
slower than proper foreign keys.

If that is a serious issue, store the enum value names as strings. It's
ok. Really.

No it's not. Performance is a major issue here, and with the number of
records that need to refer to this, we need a properly indexed & optimised
solution. Strings will kill it. Not to mention still leaving the problem of
referential integrity not being truly enforced.

We're trying to implement a best practice solution under VS 2005 and looking
to go down the whole n-unit testing before check-in route, so that's why I
was wishing to build the classes at compile time.


Martin

 

[Chris Priede]

Hi,

Not exactly best practice is it? Not to mention check
constraints will be slower than proper foreign keys.

You keep referring to this problem as one of "referential integrity".
Referential integrity exists between tables. With regards to something that
only exists outside the database, such as enum values defined in application
code, I think the issue would be better characterized as one of data
validation. Thus the suggestion of check constraints.

Since you don't like check constraints, but love foreign keys, perhaps you
should consider the enum values to names table suggestion. If you do that,
you can make the values a primary key and treat all references to it
elsewhere in the database as foreign keys -- referential integrity
accomplished.

I was wishing to build the classes at compile time.

I am not presently aware of any way to do that.

You could do it at runtime, combining the name/value table suggestion along
with a class simulating an enum type, and loading the valid names and values
from the database (hopefully only once at startup). However, that seems
worth the trouble only if there is a need to add new values without
rebuilding the application -- and I think you said there isn't.

 

[Kevin Yu [MSFT]]

Thanks for Chris's response.

Hi Martin,

I agree with Chris that we need to add constraints on this. When storing to
database, the constraints will be checked automatically. But it's not
recommended to put enums and values for all columns, as this will give a
hit on both performance and coding complexity.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."