Re: When to use integer keys?

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Wed, 24 Jan 2007 21:17:10 GMT
Message-ID: <q1Qth.4856$1x.83132@ursa-nb00s0.nbnet.nb.ca>

Kramer wrote:

>
> On Jan 23, 7:12 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>

>>What predicates does one want to express relative to BlockType?

>
>
> IsBlockOfType(BlockA,Type1)
>
>

>>Do rows
>>in the BlockType table do anything other than state that a block type
>>exists?

>
>
> A row in the BlockType table just says that that type is a valid type.
>
>

>>Do you have referential integrity constraints related to block type?
>>

>
>
> Each block should have a type, but a type can exist without a block of
> that type. I'm not sure what should happen if a type is deleted.
> That's unlikely to happen; however, if a BlockType is renamed, that
> change should apply to all blocks of that 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?

>
>
> The block type names are unique, mostly stable (most will not ever
> change; a few may change but rarely). They are also familiar.
>
> So please correct me. I know very little about designing databases but
> it seems that most of my answers point to BlockType just being one
> column and not a separate table except for the fact that BlockType
> names may change.

Yes, but stability is important. It is a design tradeoff. Both the name and the integer are unique and simple. One is familiar while the other is not. One is stable while the other is not. It's a tradeoff and there is no single right or wrong answer. Received on Wed Jan 24 2007 - 15:17:10 CST