From: Jim C. Nasby <jnasby@pervasive.com>
To: Sue Fitt <sue@inf.ed.ac.uk>
Cc: Chris <dmagick@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: setting up foreign keys
Date: Tue, 15 Aug 2006 11:01:24 -0500
Message-ID: <20060815160124.GQ27928@pervasive.com> (raw)
In-Reply-To: <44DAFA6D.4070606@inf.ed.ac.uk>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jnasby@pervasive.com, sue@inf.ed.ac.uk, dmagick@gmail.com
Subject: Re: setting up foreign keys
In-Reply-To: <20060815160124.GQ27928@pervasive.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox