Referential Integrity: Best way to reference parent table from child? *Newbie*

Home -> Community -> Usenet -> c.d.o.misc -> Referential Integrity: Best way to reference parent table from child? *Newbie*

From: Josh Gough <dasjsg_at_quequeg.gsu.edu>
Date: Fri, 30 Oct 1998 12:14:21 -0500
Message-ID: <3639F3ED.B5291CB3@quequeg.gsu.edu>

*newbie alert*

Hello, I'm starting a database project with Oracle 8 and seeking a few pointers for table design regarding efficiency and integrity

I will have one table, Links, that contains a Unique field URL. Another table, Descriptions, will contain descriptions given by different people about each Link. In order to reference a particular Link, should I repeat the URL field as the foreign key in the Descriptions table, or would it be more efficient to create an auto-incrementing sequence in the Links field which can then be used as the Primary Key in the Links table.

My two conceptions so far are these:

Links:
URL, Date
http://www.blah.com, 10-30-1998
http://www.linux.org, 10-30-1998

Descriptions:
URL,Description,Date
http://www.blah.com,'This site is......',10-30-1998 http://www.blah.com,'This site is not....',10-30-1998

In the above scenario, URL in the Descriptions table is a foreign key referencing URL of the Links table,
but this seems like quite a bit of repeated string data, so my question is wheteher the following would
be more efficient:

Links:
URL, Date,ID
http://www.blah.com, 10-30-1998, 1
http://www.linux.org, 10-30-1998, 2

Descriptions:
ID_URL,Description,Date
1, 'This site is......', 10-30-1998
1, 'This site is not....', 10-30-1998

In this scenario, one additional field is added to the parent, Links, but Descriptions much less data. Is this latter scenario the better of the two, or am I over-analyzing this? :)

Also, would it be possible to allow for Insertions of the URL by string name, but Actually have the database insert the ID into the child table? I know this would be very simple if coded that way in a script for each table, but is this something that can be facilitated at table creation time via triggers or something?
(I would like for users to be able to input the easier-to-recall URL address, but internally store the perhaps more efficient integer reference.)

Thank you,
Josh Received on Fri Oct 30 1998 - 11:14:21 CST