[thelist] (SQL) Query with Multi FK's to the same lookup

Tab Alleman Tab.Alleman at MetroGuide.com
Wed May 19 12:45:43 CDT 2004

Drew Shiel wrote:
> At 16:02 19/05/2004, Tab Alleman wrote:
>>> Frankly, if this were MySQL, at least, I'd redefine all the rating
>>> columns to ENUM('Poor','Fair','Average','Good','Excellent'); you'd
>>> use your existing numeric insert/update statements but would get a
>>> string back on selects...
>> Hard-coding like this isn't desirable in this case because if I ever
>> need to change "Excellent" to "Superior", for instance, I'd have to
>> change it in all my SQL queries instead of in the lookup table.
>    As I understand it, the SQL queries would remain numeric in all the
> queries, except for SELECTs, when the database would return the
> relevant name/word. If you came to change it, you'd only have to
> change the field type in the database. Or am I missing something?

Well, maybe it's that I misguessed what ENUM() does.  I assumed it was a
function you'd use in the context of a SELECT Query, but if it goes in
the DDL of the CREATE TABLE, then it might be useful, although it would
mean that only a DDL writer could ever change those values, which is
something I'd hope to avoid.  In any event, I don't have MySQL; I have
MS-SQL 2k, which doesn't have ENUM() per se.

More information about the thelist mailing list