Skip site navigation (1) Skip section navigation (2)

Re: setting up foreign keys

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Sue Fitt <sue(at)inf(dot)ed(dot)ac(dot)uk>
Cc: Chris <dmagick(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: setting up foreign keys
Date: 2006-08-15 16:01:24
Message-ID: 20060815160124.GQ27928@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

In response to

pgsql-performance by date

Next:From: Jim C. NasbyDate: 2006-08-15 16:25:24
Subject: Re: Postgresql Performance on an HP DL385 and
Previous:From: Sebastián BaioniDate: 2006-08-15 15:43:29
Subject: Re: Inner Join of the same table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group