What is the best way to create a coded field? I want to do something
similar to enumeration but I don't want to have to define the values at
table creation time because sometimes the end-users need to add or remove
the codes.
I've been using char binary fields in my database to this point figuring
that takes only one byte per stored code. Then the values can be ASCII
chars and would be kind of meaningful if retrieved from the database. For
instance, I might have 'f' for freshman, 'S' for Sophomore, 'J' for Junior,
and 's' for Senior. Actually, there's like 20 different categories for
students. So then I have a lookup table for codes:
create table codes (
code_type varchar(10,
code_key char binary,
code_text varchar(80)
);
Then I can do left joins to retrieve a description of the code if
necessary. For example:
INSERT INTO codes VALUES ('class', 'F', 'Freshman');
INSERT INTO codes VALUES (class', 'S', 'Sophomore');
INSERT INTO codes VALUES ('class', 'J', 'Junior');
INSERT INTO codes ('class', 's', 'Senior');
SELECT C.code_text AS academic_class
FROM students S
LEFT JOIN codes C ON (C.code_type='class' AND S.class=C.code_key);
That particular example might work better with enum but it's a deliberately
trivialized example. Most of my coded fields have 5-20 possible values.
My problem is that I've had some codes imposed upon me that are 5 chars. I
don't know if I should just start over or what. Maybe other people deal
with coded fields in a totally different way that is way better than what
I've invented.
Suggestions?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
