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_at_ursa-nb00s0.nbnet.nb.ca>
>
>
> IsBlockOfType(BlockA,Type1)
>
>
>
>
> A row in the BlockType table just says that that type is a valid 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.
>
>
>
>
> 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.
Date: Wed, 24 Jan 2007 21:17:10 GMT
Message-ID: <q1Qth.4856$1x.83132_at_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 - 22:17:10 CET