List:General Discussion« Previous MessageNext Message »
From:John Heim Date:May 4 2006 4:34pm
Subject:Coded fields
View as plain text  
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?

Thread
Coded fieldsJohn Heim4 May
  • Re: Coded fieldsAdrian Bruce5 May
  • Re: Coded fieldssheeri kritzer5 May
    • Re: Coded fieldsJohn Heim5 May
      • Re: Coded fieldssheeri kritzer5 May