> 1. Personally I find it much easier to debug alpha-numeric
> codes, rather
> than integers.

Speed. Integers resolve faster than alphanums(AFAIK)...

> I've been in the situation before where I have a number, but
> I'm not sure if
> it represents a stock code, raw material, order number, or
> customer account,
> because I scribbled it down in a hurry, without labeling it. With user
> defined codes, there is much less of a chance of having a
> customer account
> with the same value PK as a stock code. This just means
> getting into the
> habit of writing more details down, and always working with
> pen and paper
> close at hand.

Here's a complete excerpt from one of my table designs(unedited)
A reasonable naming convention goes a long way to preventing confusion.

CREATE TABLE mlogi_tlnk_warehouseloc (
int_siteid INTEGER NOT NULL,
dtm_rstamp TIMESTAMP DEFAULT 'now' NOT NULL, /* Replication Stamp */
int_location NUMERIC(18,0) NOT NULL, /* The unique location ID */
int_whouse NUMERIC(18,0) NOT NULL, /* Which warehouse is it */
int_section INTEGER NOT NULL, /* Which section of the warehouse */
int_row INTEGER NOT NULL, /* Which row of shelves in the section */
int_shelf INTEGER NOT NULL, /* Which shelf in the row */
int_pos INTEGER NOT NULL, /* Which position along the shelf */
str_note VARCHAR(20),
PRIMARY KEY(int_siteid, int_location),
UNIQUE (int_siteid, int_whouse, int_section, int_row, int_shelf,
int_pos),
FOREIGN KEY(int_siteid, int_whouse) REFERENCES mlogi_tbl_warehouse
(int_site
id, int_whouse) ON UPDATE CASCADE,
FOREIGN KEY(int_siteid, int_section) REFERENCES mlogi_tlnk_warehousesecs
(in
t_siteid, int_section) ON UPDATE CASCADE,
FOREIGN KEY(int_siteid, int_row) REFERENCES mlogi_tlnk_warehouserows
(int_si
teid, int_row) ON UPDATE CASCADE,
FOREIGN KEY(int_siteid, int_shelf) REFERENCES mlogi_tlnk_warehouseshelf
(int
_siteid, int_shelf) ON UPDATE CASCADE
);

In hindsight, that was probably a really confusing table to use as an
example - it's deep inside the logistics module of OA.
But as you can see, 'int_siteid' is used in all referential integrity, as it
exists in every single table as well.

>
> 2. Also, another drawback, is scanning a stock transaction
> table containing
> numbers that mean nothing to me by sight. If, however, the
> table contains
> the stock code themselves, it saves me having to link to the
> stock table to
> see what it actually represents. Now that I'm using Firebird,
> though (along
> with IBExpert), this is actualy becoming less of problem, as
> it is just as
> easy to run a stored proc (with all the stock descriptions
> looked up), as it
> is to look at the data in the table directly.

Stored Procedures with fast and efficient joins inside are definitely the
way to go!

> 3. The user generally still wants a stock code, and
> uniqueness still needs
> to be enforced, so the end user ALWAY has to enter a code, so
> it doesn't
> help the end user much, and it doesn't make my life easier either.

Stock item (id)'20', might have stock code '30canbeer', and it's description
is 'Carton of beer, 30 cans'
Pkey can be hidden from the user(in fact, it should, if it makes no sense)

>
> The integer based PK DOES sound like a good idea at first,
> but are there any
> real benefits?
>

Speed. Joins are faster, indexes are quicker to traverse, less disk-space.

Sure, it's a little more programming, but I love it!

N.