Re: When to use integer keys?

On Jan 23, 2:17 pm, "Kramer" <johnlkra..._at_gmail.com> 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?

At each point in time is there a valid set of Blocks that are valid so that you would want to validate each "record" of data that includes a block value against the "master list" of block values (with the ability to add to that list, most likely)? If there is no validation, then this block value is much like freeform text. If there is validation, then you could use a Block Id and a Description, but you might want to use a "code" such as REDBK for Red Block (recognizing that is likely non-sensical as a block name). Then you can have drop down lists with the descriptions (or codes or both, depending on the user requirements) and you can decide which to opt for when reporting against the data.

Also, if you use codes, whether numeric or varchar, then if a description for a block needs changing, that can be done with no mass changes to the data already in the database.

To summarize, it is likely (without knowing the full requirements) that you will want a validation table for the possible values for this attribute and that you will want to put a code, whether numeric (cryptic to user, but carries no hint of meaning) or alpha-numeric (recognizable to the user, but if description changes you are stuck with a code that doesn't align with the new description or you need to update entire database for a new code).

> Thanks in advance for any help.

Best wishes. --dawn Received on Wed Jan 24 2007 - 22:12:47 CET