I would approach this in practically the same way as yourself but used
an enum field in the student table (like what you were thinking). When
a user wants to add or remove an enum value you can build an appropriate
MODIFY statement like:
ALTER TABLE my_table MODIFY my_col ENUM('K','X') NOT NULL;
It is slightly more robust this way as depending on what SQL mode you
are running in it is possible to identify or block any incorrect values
being inserted into the student table (although this already may not be
possible depending on your interface). You would also have to change
the code_key to varchar though to allow different length and mixes of
lookup codes.
Does this help?
Ade
John Heim wrote:
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]
