db design advice

db design advice

Post by ed zappull » Wed, 14 Jul 1999 04:00:00



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.

I'm concerned about performance.

Any ideas....Ed

 
 
 

db design advice

Post by Jason Whit » Wed, 14 Jul 1999 04:00:00


Using a numeric ID which has nothing to do with the data it identifies often
leads to a more flexible design. Also, numeric matching is often more
efficient than string matching, though I don't know the specifics for Oracle
8.

--
Jason White
Staff Software Architect
Interliant - Building Global Communities


>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.

>I'm concerned about performance.

>Any ideas....Ed


 
 
 

db design advice

Post by Don Corle » Wed, 14 Jul 1999 04:00:00


Ed,

In my opinion, this is the correct way to structure your files.

In the old days, we used to have a user type in a "code", but in most GUI
designs, we have the user pick an entry from a pop-up list.

In my designs, I also have many files with a "code" field, but I try
eliminate them whenever possible. I always access records by the "id" field.
This gives you the flexibility to change or eliminate codes without
effecting your references.

Also, you should either use an autonumber/identity field for the ID, or just
call "SELECT id FROM vehicle_type ORDER BY id desc" and bump the ID, when
you write new records.

Good luck in your design!

Don
Don_Corley at msn.com


Quote:> 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.

> I'm concerned about performance.

> Any ideas....Ed

 
 
 

db design advice

Post by ed zappull » Fri, 16 Jul 1999 04:00:00


Hi Don, thanks for the advice.  It seems like I'm on the right track.

Yea, I use Oracel sequences to gen the ID.

Glad to see great minds think alike.

...ed


> Ed,

> In my opinion, this is the correct way to structure your files.

> In the old days, we used to have a user type in a "code", but in most GUI
> designs, we have the user pick an entry from a pop-up list.

> In my designs, I also have many files with a "code" field, but I try
> eliminate them whenever possible. I always access records by the "id"
field.
> This gives you the flexibility to change or eliminate codes without
> effecting your references.

> Also, you should either use an autonumber/identity field for the ID, or
just
> call "SELECT id FROM vehicle_type ORDER BY id desc" and bump the ID, when
> you write new records.

> Good luck in your design!

> Don
> Don_Corley at msn.com



> > 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.

> > I'm concerned about performance.

> > Any ideas....Ed

 
 
 

db design advice

Post by Ted Kinsle » Fri, 16 Jul 1999 04:00:00


I hope you do not try this with multiple users simultaneously using the
system.  It will not work.  Think about what happens when two users execute the
process at just the right time.  One of them will have the wrong id.  You would
have to lock the table to ensure the proper ID.  This is not a very good
solution.  Consider using a sequence or another table which contains your next
id to use.  If you use the another table concept, all that is required is to
update the record and query the table then do the commit.  This will guarentee
uniqueness.
Quote:> > Also, you should either use an autonumber/identity field for the ID, or
> just
> > call "SELECT id FROM vehicle_type ORDER BY id desc" and bump the ID, when
> > you write new records.

 
 
 

db design advice

Post by Wade Warren » Sat, 17 Jul 1999 04:00:00



> 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

 
 
 

db design advice

Post by Ed Zappull » Mon, 19 Jul 1999 04:00:00


Hi Wade,   What you describe is exactly my delemia.  But is seems that there
are efficiencies of storage and comparison which do make having the id
column usefull enoough to put up with it.  Thanks for your help....ed



> > 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

 
 
 

db design advice

Post by Dant » Wed, 28 Jul 1999 04:00:00


Ed,
if you are sure that you will NOT update the "cd" ever, then you could move your
PK to cd.
In the case you have to consider that this might be changed by the user, you
should not.

The reason is quite simple:
If you update the PK which is referenced already, you will have first to
de-reference all the children, change the PK-value and reference it to the new
PK-value

Regards
Dante


> 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.

> I'm concerned about performance.

> Any ideas....Ed

 
 
 

db design advice

Post by Dant » Wed, 28 Jul 1999 04:00:00


Ed,
of course you could take advantage of the "update cascade" for FK ... if you are
willing to take the downside (locks, performance, etc)

Regards
Dante


> Ed,
> if you are sure that you will NOT update the "cd" ever, then you could move your
> PK to cd.
> In the case you have to consider that this might be changed by the user, you
> should not.

> The reason is quite simple:
> If you update the PK which is referenced already, you will have first to
> de-reference all the children, change the PK-value and reference it to the new
> PK-value

> Regards
> Dante


> > 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.

> > I'm concerned about performance.

> > Any ideas....Ed

 
 
 

db design advice

Post by Richard Draucke » Wed, 28 Jul 1999 04:00:00


Doesn't incrementing from the result of "SELECT MAX(id)"
prevent this problem?
Richard D.

> I hope you do not try this with multiple users simultaneously using the
> system.  It will not work.  Think about what happens when two users execute the
> process at just the right time.  One of them will have the wrong id.  You would
> have to lock the table to ensure the proper ID.  This is not a very good
> solution.  Consider using a sequence or another table which contains your next
> id to use.  If you use the another table concept, all that is required is to
> update the record and query the table then do the commit.  This will guarentee
> uniqueness.

> > > Also, you should either use an autonumber/identity field for the ID, or
> > just
> > > call "SELECT id FROM vehicle_type ORDER BY id desc" and bump the ID, when
> > > you write new records.

 
 
 

db design advice

Post by Dant » Wed, 28 Jul 1999 04:00:00


Richard,
how should it ?
Consider table EMP with MAX(EMPNO) = 100.
If user A and B select from EMP, both will get 100, and will take 101 for the next
available value.

There is a easy solution to this, called "sequence".
Oracle RDBMS (and maybe other RDBMS) provides such an object which is independent
from transactions.
or taken from Oracle Designer:
you create a "code control sequence", which holds an UID, the last fetch value and as
a add-on maybe an increment, maxvalue.
The applications select from that table (in the primitive version, they will have to
lock the table) gets the current_value and increments it.
This has as well the benefit that you are not wasting performance as you would be
doing by selecting via MAX(x).

Regards
Dante


> Doesn't incrementing from the result of "SELECT MAX(id)"
> prevent this problem?
> Richard D.


> > I hope you do not try this with multiple users simultaneously using the
> > system.  It will not work.  Think about what happens when two users execute the
> > process at just the right time.  One of them will have the wrong id.  You would
> > have to lock the table to ensure the proper ID.  This is not a very good
> > solution.  Consider using a sequence or another table which contains your next
> > id to use.  If you use the another table concept, all that is required is to
> > update the record and query the table then do the commit.  This will guarentee
> > uniqueness.

> > > > Also, you should either use an autonumber/identity field for the ID, or
> > > just
> > > > call "SELECT id FROM vehicle_type ORDER BY id desc" and bump the ID, when
> > > > you write new records.

 
 
 

1. Need Db design advice

I have a database that is going to be used for our personnel dept. We will 4
main tables of appx 70k,70k & 20k,20k records (because of the 255 field
limit). There is a lot of "coded" data in the records of these tables. We
have appx 150 decode tables to translate the data. Now I am guessing that if
I write stored procedures that may need to join in 10 or 12 translate tables
at a time these stored procedures will run horribly. Is this the way to do
it?? Is there a better design that someone can push me in the direction of
??  I am not being rushed to get this into production, but I only want to do
it once if at all possible.........

--
John

2. MainMenu. how do I run my apps ?

3. Need DB design advice

4. Generic procedure with output variable

5. DB Design Advice Needed

6. SQL Server Performance worse than Access!

7. DB design advice needed

8. Corel's Pdox Newsgroups

9. Need advice on DB design (Access)

10. Relational DB Design Woes (Any Advice?)

11. Advice on big kid db design

12. DB modelling / Rel. DB design