Date: Tue, 15 Aug 2006 11:01:24 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Sue Fitt <sue@inf.ed.ac.uk>
Cc: Chris <dmagick@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: setting up foreign keys
Message-ID: <20060815160124.GQ27928@pervasive.com>
References: <44DAE8CF.1030107@inf.ed.ac.uk> <44DAEB04.9050306@gmail.com>
	<44DAF6B7.1090800@inf.ed.ac.uk> <44DAF8B9.2080000@gmail.com>
	<44DAFA6D.4070606@inf.ed.ac.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <44DAFA6D.4070606@inf.ed.ac.uk>
User-Agent: Mutt/1.5.11

On Thu, Aug 10, 2006 at 10:20:45AM +0100, Sue Fitt wrote:
> Well they don't necessarily have the same value!
> 
> It's a dictionary with cross-referenced words, e.g. 'bring' and 
> 'brought' are both headwords in the dictionary, but 'brought' is 
> cross-referenced to 'bring'. So, the table stores the information (using 
> integer id's rather than words) that
>    bring: bring
>    brought: see bring
>    sing: sing
>    sang: see sing
> etc.
 
If that's actually how it's represented (a row for both sing and song)
it's denormalized. My rule of thumb is "normalize 'til it hurts,
denormalize 'til it works", meaning only denormalize if you need to for
performance reasons. In this case, it's certainly possible that
performance-wise you're best off denormalized, but you might want to
experiment and find out.

BTW, the normalized way to store this info would be to only put records
in that table for brought and song.

> Sue
> 
> Chris wrote:
> >Sue Fitt wrote:
> >>Thanks Chris and Chris, you've solved it.
> >>
> >>I had a gui open that connects to the database. It was doing nothing 
> >>(and not preventing me adding to or altering headwords_core via 
> >>psql), but having closed it the table is instantly created. Weird.
> >>
> >>BTW, referencing the same column twice is deliberate, it's a 
> >>cross-reference.
> >
> >The same column and the same table?
> >
> >Same column different table I could understand but not the same column 
> >& table ;)
> >
> >I'm sure there's a reason for it though :)
> >
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461