On 13/01/12 05:56, David Johnston wrote: <blockquote cite="mid:016a01ccd14b$1f40baf0$5dc230d0$@yahoo.com"
type="cite"><prewrap="">[...]
Contrary to my earlier advice assigning a sequential ID (thus using a
numeric TYPE) is one of the exceptions where you can use a number even
though you cannot meaningfully perform arithmetic on the values. The reason
you would use a numeric value instead of a character is that the value
itself is arbitrary and the space required to store a number is less than
the space required to store a string of the same length.
There are many points-of-view regarding whether to use "serial" PRIMARY KEYs
but regardless of whether you add one or not you should try and define a
UNIQUE constraint on the table by using meaningful values. However, for
things like Orders this is generally not possible and so you would want to
generate a sequential identifier for every record.
David J.
</pre></blockquote><p class="western" style="margin-bottom: 0cm">Hmm...<br /><br /> In any database I design, I
deliberatelykeep primary keys quite separate from any user visible values. In order to minimise changes to the database
resultingfrom business format changes, such as redoing the format of customer numbers for marketing purposes. <br /><br
/>Also, in a chain of parent child tables, the child only needs to know how to get its parent, it does not need to know
itsgrandparents! One insurance package I worked on, had the primary key of a child table a concatenation of its
parent'sprimary key with a unique field. So some child tables had multiple character field as their primary keys,
potentiallyhave keys of some 45 or more characters!<p class="western" style="margin-bottom: 0cm">I normally use
integersfor the primary key type. This makes keeping track of records in a program much easier.<p class="western"
style="margin-bottom:0cm">However, I do not usually expose these keys to users, and it would be rare (if ever) to have
themas fields in search boxes.<br /><br /><br /> Cheers,<br /> Gavin
