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
