pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dado Feigenblatt <dado@wildbrain.com>
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com
Subject: Re: indexing of hierarchical data
Date: Fri, 06 Jul 2001 17:20:36 -0700
Message-ID: <3B4655D4.465A52F4@wildbrain.com> (raw)
References: <web-82025@davinci.ethosmedia.com>

<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
Josh Berkus wrote:
<blockquote TYPE=CITE>Dado,
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Yeah, me again.&nbsp; What
can I say?&nbsp; I'm procrastinating, and list stuff
<br>is a great way to do it.&nbsp; And I prefer design theory issues.
<p>project&nbsp;&nbsp;&nbsp; sequence&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
shot
<p>CopFilm1&nbsp;&nbsp; alley shooting&nbsp;&nbsp; death of the bad guy
<br>CopFilm2&nbsp;&nbsp; car chase&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
death of the bad guy
<br>CopFilm3&nbsp;&nbsp; car chase&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
death of the bad guy
<p>At first I was indexing the shots just buy shot_ID (serial), and storing
<br>the sequence_ID it belongs to.
<br>On the sequence record, I was storing the project_ID it belongs to.
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; This sounds fine so far.
<br>&nbsp;
<p>So if I wanted to select CopFilm3, car chase, death of the bad guy
<br>I had to find the ID of the project CopFilm3, the ID of the sequence
car
<br>chase belonging to that project and then shot death of the bad guy
<br>belonging to that sequence.
<br>As most of the operations happen at the shot level, for performance
<br>reasons I think it might be better to store the project and sequence
<br>with the shot, so I don't have to perform any joins.
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; What's wrong with joins?
<p>Also, projects and sequences have alphabetical codes assigned to them,
<br>which is usually the prefered way of accessing the data.
<br>So, it is my impression that I should store those codes in the shot
as
<br>foreign keys with on update cascade
<br>should someone decide to rename projects and sequences, and their codes,
<br>which happens.
<br>Is this approach ok or should I stick to serial ID's and make the
<br>lookups?
<br>Any comment on problems like this?
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Yes.&nbsp; You want to have
as primary and foriegn keys values that do not
<br>change over the lifetime of the record.&nbsp; Any time that you choose
<br>instead user-modifiable records you are introducing a world of headaches
<br>and trigger maintainence.
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; The solution to the above
design problem is simple:
<p>table projects
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; project_ID SERIAL PRIMARY
KEY
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; project_code VARCHAR(5)
NOT NULL
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; project_name VARCHAR(100)
NOT NULL
<p>table sequences
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sequence_ID SERIAL PRIMARY
KEY
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; project_ID INT NOT NULL
REFERENCES projects(project_ID)
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sequence_code VARCHAR(5)
NOT NULL
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sequence_name VARCHAR(100)
NOT NULL
<p>table shots
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; shot_ID SERIAL PRIMARY KEY
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sequence_ID INT NOT NULL
REFERENCES sequences(sequence_ID)
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; shot_code VARCHAR(5) NOT
NULL
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; shot_name VARCHAR(100) NOT
NULL
<p>CREATE VIEW vw_shots AS
<br>SELECT project_code, project_name, sequence_code, sequence_name,
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; shot_code, shot_name
<br>FROM projects JOIN sequences USING (project_ID)
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; JOIN shots USING (sequence_ID);
<p>Then you users can access this view, and search by codes without being
<br>aware that the numerical ID's even exist.&nbsp; For that matter, you
can
<br>impose UNIQUE constraints on codes within their context without tying
up
<br>those codes for all time or preventing your users from changing the
<br>codes.</blockquote>
I think that's where experience comes in, uh?
<br>I haven't thought of creating a view that way.
<br>That's another very helpfull hint.
<br>I think if we don't get a consulting budget anytime soon I'll have
to get you dinner ;)
<p>Thanks.
<p>PS: can't your e-mail client insert >'s on replies? It's kind of heard
to sift through.
<pre>--&nbsp;
Dado Feigenblatt&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Wild Brain, Inc.&nbsp;&nbsp;&nbsp;
Technical Director&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (415) 553-8000 x???
dado@wildbrain.com&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; San Francisco, CA.</pre>
&nbsp;</html>




view thread (3+ messages)

Message-ID: <3B4655D4.465A52F4@wildbrain.com>
Permalink:  ../3B4655D4.465A52F4@wildbrain.com/
Also on:    postgresql.org/message-id/3B4655D4.465A52F4@wildbrain.com

 · 

reply

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-sql@postgresql.org
  Cc: dado@wildbrain.com, josh@agliodbs.com
  Subject: Re: indexing of hierarchical data
  In-Reply-To: <3B4655D4.465A52F4@wildbrain.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