Re: UNIQUE constraint on non-"key" columns

spamtrap-at-janhv_at_comhem.se wrote:

> Hello.
>
> After waking up at 04:30 and not being able to go to sleep again, a
> thought crossed my mind, regarding a table of inventory items. You
> have a table like this:
>
> CREATE TABLE items
> (
> item_no VARCHAR(15) NOT NULL,
> item_descr VARCHAR(40) NOT NULL
> UNIQUE (item_no)
> )
>
> My thought was that item_descr rather should be UNIQUE as well,
> because how would you otherwise tell one item from another, except for
> the item_no? This implies that item_no is nothing else than a
> shorthand for item_descr.
>
> An example.
>
> Suppose you are into selling clothes, and you have the following
> stock:
>
> item_no item_descr
> ------- ----------
> shrt1B Shirt, black
> shrt1BHQ Shirt, black
>
> (These are unisize and unisex models)
>
> They differ only in price, because of different manufacturer. Would
> you add something like "High Quality" to the description of the more
> expensive (and therefore of higher quality) shirt, or would you make
> that out of the item_no? Suppose you use an EAN or the like? In that
> case, you will not be able to tell anything about the shirt from the
> item_no, so a black shirt is like any other black shirt, given the
> item_descr above.
>

I have only seen one apparel package in any detail, but I believe it is rather common to use up to 4 keys to define a "style". These are "style" "color" "label" and "dimension". Each of these is a foreign key to a lookup table, and there may be other cross-references that define valid combinations. The style is the loosest, being something like "formal men's shirt." The color is then black,white, etc. The label is the label you are selling under, so yes, that famous label that does look exactly like the department store shirt is in fact made in the same shop in Asia. Dimension tends to be S, M, L. Then you have more size dimensions within that.

The general answer though to any question of this type is to add more atomic columns to an incomplete key, columns which are defined in other tables with allowed values. Then you create a composite key of meaningful information, rather than just including a person's idea of a description into the key.

-- 
Kenneth Downs
<?php $sig_block="Variable scope? What's that?";?>