|Subject||RE: [firebird-support] PK database design was RE: SP hanging|
> 1. Personally I find it much easier to debug alpha-numericSpeed. Integers resolve faster than alphanums(AFAIK)...
> codes, rather
> than integers.
> I've been in the situation before where I have a number, butHere's a complete excerpt from one of my table designs(unedited)
> 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.
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 */
PRIMARY KEY(int_siteid, int_location),
UNIQUE (int_siteid, int_whouse, int_section, int_row, int_shelf,
FOREIGN KEY(int_siteid, int_whouse) REFERENCES mlogi_tbl_warehouse
id, int_whouse) ON UPDATE CASCADE,
FOREIGN KEY(int_siteid, int_section) REFERENCES mlogi_tlnk_warehousesecs
t_siteid, int_section) ON UPDATE CASCADE,
FOREIGN KEY(int_siteid, int_row) REFERENCES mlogi_tlnk_warehouserows
teid, int_row) ON UPDATE CASCADE,
FOREIGN KEY(int_siteid, int_shelf) REFERENCES mlogi_tlnk_warehouseshelf
_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.
>Stored Procedures with fast and efficient joins inside are definitely the
> 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.
way to go!
> 3. The user generally still wants a stock code, andStock item (id)'20', might have stock code '30canbeer', and it's description
> 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.
is 'Carton of beer, 30 cans'
Pkey can be hidden from the user(in fact, it should, if it makes no sense)
>Speed. Joins are faster, indexes are quicker to traverse, less disk-space.
> The integer based PK DOES sound like a good idea at first,
> but are there any
> real benefits?
Sure, it's a little more programming, but I love it!