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

Josh Gough schreef:

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

I would definitely go for the sequence key. If you use the URL as a key, and it changes (and that's not so incommon) than you'll have to change stuff in your keys and I don't think this is advisable.
Furthermore, if you use the sequence-numbers as keys, you simply have to change one string (which is not a key then) in your parent table when the URL changes.

But then again, there are probably other reasons that justify the use of the URL's as keys that I didn't think of....

HTH Jochen. Received on Mon Nov 09 1998 - 12:04:03 CST