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.

What predicates does one want to express relative to BlockType? Do rows in the BlockType table do anything other than state that a block type exists? Do you have referential integrity constraints related to block type?

Once you answer those, if you decide you need a BlockTypes table, the design criteria for candidate keys are uniqueness, stability, familiarity and simplicity.

In my opinion, a character string is as simple as an integer. Are the block type names unique, stable and familiar? Received on Wed Jan 24 2007 - 04:12:37 CET