Package DB Schema v3

[Karel Zak]

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 at redhat.com>