Re: UNIQUE constraint on non-"key" columns

In message <8gbdq0ppdg16gvp9ra2ch8at9qve1g38u6_at_4ax.com>, spamtrap-at-janhv_at_comhem.se writes
>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.

It's a surrogate key. You could live without it and if you want to fully normalise the table you will probably have to. The item_no is likely to be completely dependent on the item_descr. Of course in theory you could have two items with the same description ("Black Shirt") if you don't care which you wear.

>
>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.
>
>So, is it only fatigue or are there any valid points in this
>reasoning?

There are some. A retailer may hold interchangeable items from different manufacturers, so they wouldn't care who the manufacturer was. The quality control department would, so they could trace customers returns back to a particular manufacturer or perhaps even to a specific batch number made on a known date. When you build an aircraft you may be able to use any 4mm bolt but the QA department will be able to trace the bolt back and identify exactly who made it, where and when. They may even need to trace the batch of steel the bolt was made from.

Just what amount of detail you need depends on what you need the detail for. Data and database systems don't exist in splendid isolation. The data means something to the people who use the database. To be a good data manager you need to understand the meaning of the data and not just its syntax.

-- 
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.