Re: When to use integer keys?

Kramer wrote:
> Hi. I'd like to know when it is appropriate to use an integer and when
> to just use the text. For example, I have a database here which
> represents input to an application. One table Input stores many
> records each of which corresponds to a "block" (doesn't matter what
> that means). We now need to store the block type. Each block type is
> text that could be as much as 30 characters. There seem to be two ways
> to represent this.
>
> 1) Just store the name:
>
> Table Name: Input
> ...
> BlockType : Varchar(30)
>
> 2) Seperate Tables:
>
> Table Name: Input
> ...
> BlockTypeID : Integer -----
> |
> Table Name: BlockTypes |
> BlockTypeID : Integer<----/
> BlockTypeName : VarChar(30)
>
> Now there will be many more entries in the table Input than in the
> table BlockTypes. Thus the two seperate table would seem to save some
> space, but I'm not sure if the space is critical. Are other advantages
> for using two separate tables? Are there advantages for using one
> table?
>
> Thanks in advance for any help.

You haven't given any sound reason for preferring either one table or two. We can't know for sure that it will save storage space without knowing the internals of the (unspecified) DBMS product you are using. Nor is it clear that the savings on storage alone would justify the additional complexity of an extra table.

The prudent course is to design a logical model independently from storage considerations. Storage requirements are determined by the physical model not the logical.

-- 
David Portas