Message-ID: <4342F61A.1010903@freedomcircle.net>
Date: Tue, 04 Oct 2005 17:37:30 -0400
From: Joe <svn@freedomcircle.net>
Organization: Freedom Circle, LLC
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: "Jim C. Nasby" <jnasby@pervasive.com>
Cc: Gavin Sherry <swm@alcove.com.au>, Magnus Hagander <mha@sollentuna.net>,
	pgsql-performance@postgresql.org
Subject: Re: Comparative performance
References: <6BCB9D8A16AC4241919521715F4D8BCE92E6D6@algol.sollentuna.se>
	<433BDB0B.8010002@freedomcircle.net>
	<Pine.LNX.4.58.0509292230310.26600@linuxworld.com.au>
	<433BE1A0.5000807@freedomcircle.net>
	<20051004203102.GU40138@pervasive.com>
In-Reply-To: <20051004203102.GU40138@pervasive.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Jim C. Nasby wrote:
> Make sure these indexes exist if you'll be updating or inserting into
> entry:
> 
> CREATE INDEX topic__subject_id ON topic(subject_id);
> CREATE INDEX topic__actor_id ON topic(actor_id);

Actually, topic's primary key is topic_id.

> Also, the fact that subject and actor both point to topic along with
> subject_type and actor_type make me suspect that your design is
> de-normalized. Of course there's no way to know without more info.

Yes, the design is denormalized.  The reason is that a book or article is 
usually by a single author (an "actor" topic) and it will be listed under one 
main topic (a "subject" topic).  There's a topic_entry table where additional 
actors and subjects can be added.

It's somewhat ironic because I used to teach and/or preach normalization and the 
"goodness" of a 3NF+ design (also about having the database do aggregation and 
sorting as you mentioned in your other email).

> FWIW, I usually use timestamptz for both created and updated fields.

IIRC 'created' ended up as a DATE because MySQL 4 has a restriction about a 
single TIMESTAMP column per table taking the default value of current_timestamp.

Joe