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

On Fri, 30 Oct 1998 12:14:21 -0500, Josh Gough <dasjsg_at_quequeg.gsu.edu> wrote:

>*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? :)

I think you're unnecessarily repeating data. Unless each URL will have multiple descriptions, just use one table with URL, Date and description fields. Oracle will not suffer simply because you have a large varchar field as one of the columns.

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

Sure, it should be easy.

Ciao
Fuzzy
:-) Received on Sun Nov 01 1998 - 18:17:51 CST