> Hi all, I'm looking for some advice. I'm using Oracle 8.0.5. What I'm
> interested in is the design of reference type tables. Like a vehicle type.
> My current standard is to make these tables like this:
> create table vehicle_type
> (
> id integer not null, -- PK
> cd varchar2(6) not null, -- Unique; usually less than 4 chars also.
> descp varchar2(75) null
> )
> Then to reference them in other tables by Id.
> I'm thinking that the id field is not needed and I can use the code field.
<snip>
The id field may truly be unneeded.
I work with a system where we have:
create table trade (
trade_id integer not null primary key,
currency_id integer not null references currency (currency_id),
...
create table currency (
currency_id integer not null primary key,
currency_code char(3) not null unique, -- USD, GBP, JPY, etc...
currency_name varchar(40) not null, -- US Dollar, etc...
...
We've also got similar, id-based reference tables for countries,
product types (equity, fixed, FX, etc.), and roles (trading, clearing, etc.)
IMHO, it just isn't worth it. I mean, it's not like USD is going
to mean some other currency next year or anything.
Further, to keep your SQL simple and clear, you'll probably
be creating a v_trade view that joins in the currency table.
And, if you've gone whole-hog on the id route, your views
will be joining in a lot of other tables. And practically,
you'll soon be joining your views against other views.
In Sybase, this means you'll soon bump up against
the 16 table maximum for "where" clauses.
I would much prefer (at least in this case),
create table currency (
currency_code char(3) not null primary key,
currency_name varchar(40) not null,
....
and
create table trade (
trade_id integer not null primary key,
currency_code char(3) not null references currency (currency_code),
....
If the codes are generally accepted in your business domain,
and are unlikely to change their meaning, and are used primarily
for reference data, then I say just use 'em.
--wade
by