Re: Table design question

From: Pablo Sanchez <pablo_at_dev.null>
Date: Thu, 22 Aug 2002 16:18:58 -0600
Message-ID: <3d656358$1_4@news.teranews.com>

"PeterS" <member_at_dbfoums.com> wrote in message news:1724237.1029973526_at_dbforums.com...

>

> Hello everybody.
> I have a table where foreign key references the same table's primary
> key. Primary key is just one column as well as foreign key.
> Is it good design?
> Would it be better to create a separate lookup table?
> Foreign key column contains parents of a primary key column.

As another poster posted (how many posts can a poster post ... j/k!), it's common to have recursive relationships. I'm a proponent of surrogate keys which in this case would simplify things a bit ... just my opinion and has been flogged to death here before ... but here's how I'd do it:

table_x

    id NUMBER(16) NOT NULL /* surrogate key */     ...
    parent_id NUMBER(16) NULL /* reference my parents */

Thx!

--
Pablo Sanchez, High-Performance Database Engineering
mailto:pablo_at_hpdbe.com
http://www.hpdbe.com
Available for short-term and long-term contracts

Received on Thu Aug 22 2002 - 17:18:58 CDT