Hi,

>Just to explain sets and enums
>
>ENUMs in MySQL allow you to define a set of allowed values for the field
>
> i.e. 'cat',dog',....
>the field can only contain one of the values listed above, so far you
>could use CONSTRAINTS (I think!). The twist is, when the table is
>created
>each of these values is given a numerical value as well, so 'cat'=1,
>'dog'=2 etc
>When you use the fields in querys and functions the value returned
>depends on the context in which it is used, if you treat the field as a
>number then that is what will be returned, if you treat it as a text
>string then 'cat' or 'dog' etc will be returned. The same also applies
>to INSERTs etc

These can be solved in multiple ways. You could create a small lookup
table with name/value pair combinations.

1 DOG
2 CAT
3 COW

And use the numerical values in your table, or even the text value.
Then, create a Foreign Key constraint or triggers to preserve data
integrity.

I also use domains for this purpose. I have one domain called
BESTEL_EENHEID and use this domain where I need such a value. It's
defined as:

BESTEL_EENHEID, char(2), check value in ('DS', 'DK', 'LG, 'ST')

>SETs are very similar but evaluate the allowed value as 1,2,4,8,16,32,64
>etc therefore allowing multiple values in a field. The information in
>the tables ends up being very readable, but still allows me to treat the
>fields as numeric (using a bitmask to remove a value from the field is a
>lot easier than recreating the string without that value).

I don't know of a way to end up with a readable form, except for
using a stored procedure (perhaps a computed column in combination
with a procedure) or a UDF...


Martijn Tonies
InterBase Workbench - the developer tool for InterBase and Firebird
http://www.interbaseworkbench.com


[Non-text portions of this message have been removed]