Package DB Schema v3

[Elliot Lee]

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'.

If you want to cut down on storage space, check the field types and  
specify maximum sizes. In this case, varchar(1) would be a more  
appropriate type. Since the database will wind up storing this field  
using at least 4 bytes of space for alignment purposes, might as well  
take it to varchar(3) or so to also allow for future expansion (and  
maybe a bit more readability).

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).

</stuff you never wanted to know about choosing types for DB columns> :)

Best,
-- Elliot