John,
You're close -- If a single case-sensitive letter won't help (or isn't
descriptive enough -- is 'S' sophomore or senior or special student?)
The best way to do it is NOT to make new codes. If they're giving you
codes, use theirs -- why make up a new system if you don't have to?
create table codes (
code_type varchar(10,
code_key varchar(5),
code_text varchar(80)
);
and then you can change your own codes from 's' and 'S' to 'soph' and
'sen' or whatever. Or just use whatever they use for those statuses.
Of course, if you're dealing with registrars from different schools
who may use the same code for 2 different things, then you might want
to use a surrogate key (ie, autoincrement field). But if they're
giving you special codes, why make up your own?
-Sheeri
On 5/4/06, John Heim <[EMAIL PROTECTED]> 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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
