What the best way to self-document "codes" in a SQL based application?

Question

Is there any way to implement self-documenting enumerations in "standard SQL"?

   EXAMPLE:
   Column: PlayMode
   Legal values: 0=Quiet, 1=League Practice, 2=League Play, 3=Open Play, 4=Cross Play

What I've always done is just define the field as "char(1)" or "int", and define the mnemonic ("league practice") as a comment in the code.

Any BETTER suggestions?

I'd definitely prefer using standard SQL, so database type (MySql, SQL Server, Oracle, etc) shouldn't matter. I'd also prefer using any application language (C, C#, Java, etc), so programming language shouldn't matter, either.

PS: It's my understanding that using a second table - to map a code to a description, for example "table playmodes (char(1) id, varchar(10) name)" - is very expensive. Is this necessarily correct?

Database enums - pros and cons: stackoverflow.com/questions/336846/database-enums-pros-and-cons — KM.
– KM., Commented Jan 5, 2012 at 18:58

Nicholas Carey · Accepted Answer · 2012-01-05 19:02:09Z

4

The normal way is to use a static lookup table, sometimes called a "domain table" (because its purpose is to restrict the domain of a column variable.)

It's up to you to keep the underlying values of any enums or the like in sync with the values in the database (you might write a code generator to generates the enum from the domain table that gets invoked when the something in the domain table gets changed.)

Here's an example:

--
-- the domain table
--
create table dbo.play_mode
(
  id          int         not null primary key clustered ,
  description varchar(32) not null unique nonclustered   ,
)

insert dbo.play_mode values ( 0 , "Quiet"          )
insert dbo.play_mode values ( 1 , "LeaguePractice" )
insert dbo.play_mode values ( 2 , "LeaguePlay"     )
insert dbo.play_mode values ( 3 , "OpenPlay"       )
insert dbo.play_mode values ( 4 , "CrossPlay"      )

--
-- A table referencing the domain table. The column playmode_id is constrained to
-- on of the values contained in the domain table playmode.
--
create table dbo.game
(
  id          int not null primary key clustered ,
  team1_id    int not null foreign key references dbo.team(      id ) ,
  team2_id    int not null foreign key references dbo.team(      id ) ,
  playmode_id int not null foreign key references dbo.play_mode( id ) ,
)
go

Some people for reasons of "economy" might suggest using a single catch-all table for all such code, but in my experience, that ultimately leads to confusion. Best practice is a single small table for each set of discrete values.

answered Jan 5, 2012 at 19:02

Nicholas Carey

75.1k16 gold badges96 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

paulsm4 Over a year ago

So it sounds like a "domain table" is probably not "inefficient", and definitely is "the right way to do it". +1 for teaching me the right term - "domain table". Thank you :)

KM. · Accepted Answer · 2012-01-05 18:48:40Z

2

add a foreign key to "codes" table.

the codes table would have the PK be the code value, add a string description column where you enter in the description of the value.

table: PlayModes

Columns: PlayMode     number  --primary key
         Description  string

I can't see this as being very expensive, databases are based on joining tables like this.

answered Jan 5, 2012 at 18:48

KM.

104k34 gold badges183 silver badges214 bronze badges

Comments

aF. · Accepted Answer · 2012-01-05 18:51:56Z

1

That information should be in database somewhere and not on comments.

So, you should have a table containing that codes and prolly a FK on your table to it.

answered Jan 5, 2012 at 18:51

aF.

67k45 gold badges141 silver badges200 bronze badges

Comments

Philip Kelley · Accepted Answer · 2012-01-05 19:39:40Z

I agree with @Nicholas Carey (+1): Static data table with two columns, say “Key” or “ID” and “Description”, with foreign key constraints on all tables using the codes. Often the ID columns are simple surrogate keys (1, 2, 3, etc., with no significance attached to the value), but when reasonable I go a step further and use “special” codes. Following are a few examples.

If the values are a sequence (say, Ordered, Paid, Processed, Shipped), I might use 1, 2, 3, 4, to indicate sequence. This can make things easier if you want to find all “up through” a give stages, such as all orders that have not yet been shipped (ID < 4). If you are into planning ahead, make them 10, 20, 30, 40; this will allow you to add values “in between” existing values, if/when new codes or statuses come along. (Yes, you cannot and should not try to anticipate everything and anything that might have to be done some day, but a bit of pre-planning like this can make some changes that much simpler.)

Keys/Ids are often integers (1 byte, 2 byte, 4 byte, whatever). There’s little cost to make them character values (1 char, 2 char, 3, char, 4 char). That’s character, not variable character. Done this way, you can have mnemonics on your codes, such as

O, P, R, S
Or, Pd, Pr, Sh
Ordr, Paid, Proc, Ship

…or whatever floats your boat. Done this way, I have found that it can save a lot of time when analyzing or debugging. You still want the lookup table, for relational integrity as well as a reminder for the more obscure codes.

+1 for the idea of "domain table" + "mnemonic codes". Thank you!

Collectives™ on Stack Overflow

What the best way to self-document "codes" in a SQL based application?

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related