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 4 5539
Hi,
Martin Pritchard wrote: 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...es_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.
--
Chris Priede
>> 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":
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
Hi,
Martin Pritchard wrote: 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.
--
Chris Priede
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." This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Florian |
last post by:
I need to set multiple values for some SQL statements, for example
SET NUMERIC_ROUNDABORT OFF
GO
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIF
IER,ANSI_NULLS...
|
by: Iqbal |
last post by:
Hi,
I am getting the following error when I run a stored procedure in
which I am inserting/deleting data from a view that selects from a
remote table.
INSERT failed because the following SET...
|
by: TheNortonZ |
last post by:
We are using MSDE 2000 and ADO.Net for our database/datalayer in our client
server based app.
The application is not expected to have more than one user because the whole
thing is installed on a...
|
by: Mark Lees |
last post by:
I split a database. Back end (be) is stored on a server. Front end
(fe) is stored locally on one workstation.
I will need to make changes to the forms and reports on the fe on a
different...
|
by: narshe |
last post by:
I haven't come across an elegant way to do this yet, and would like to
know what other people have come up with.
There are two tables:
CREATE TABLE EmployeeStatus
(
pkId int not null primary...
|
by: ward |
last post by:
Good morning everyone.
I'm building a very simple content management site that tracks
"tasks."
The options available are:
1. Add Task
2. Edit Task
3. View Task
4. Print Task
|
by: Jay |
last post by:
In C# I can set up an Enum so that number are represented as keywords, which is very useful.
Is there such a datatype in a database?
I suppose I could use an extra table, with the ID column as...
|
by: Jon Slaughter |
last post by:
is there a simple way to "step" through enums? I have a button that I want
to click and have it "cycle" through a set of states defined by enums but
the only way I can think of doing this...
|
by: =?Utf-8?B?Sm9uYXRoYW4gU21pdGg=?= |
last post by:
I have a class as follows:
class GamesConsole
{
public int iReference;
public enum Maker {Nintendo, Sega, Sony, Panasonic}
}
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM).
In this month's session, the creator of the excellent VBE...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
| |