On Wed, Dec 06, 2006 at 11:19:29AM -0800, Toshio Kuratomi wrote: > On Wed, 2006-12-06 at 09:19 -0500, Elliot Lee wrote: > > On Dec 5, 2006, at 2:32 PM, Toshio Kuratomi wrote: > > >> status text not null default 'D' > > >> check ( status IN ('D','A','M','E','R') ) > > >> > > > What's the justification? Here's my reasoning: > > > [Single letter code] > > > + Harder to misspell > > > + Takes less storage space > > > [Full words] > > > > You're sticking the status into a column of type 'TEXT', though. TEXT > > is not even like varchar which defaults to 1024 characters or so of > > storage. I believe TEXT tells the database 'be ready to store between > > zero and 4 billion bytes in this column'. > > > > varchar and text have had equivalent performance and storage in Definitely true. All internal PostgreSQL routines uses same type (text) for almost all operations. The varchar(n) makes sense if you want to restrict something in your data model, but it's not important for performance and storage. > > To balance fast indexing/comparison, small storage space, and room > > for expansion, it's often easier to use INTEGER for these type of > > columns, and then assign meaning to those values elsewhere (either > > through a separate table that translates values to strings, or by > > #define-like constants in the source code). > > > I think we'll do this with INTEGER and a separate table because Jeffrey > Ollie's proposal to allow translations to the status codes makes sense. It depends on number of status codes. You can use 1 or 2 chars as a primary key for status table (instead integers). It's better for humans, because simple selects (without join to status table) are still readable. Karel -- Karel Zak <kzak@xxxxxxxxxx>