Re: indexing of hierarchical data

From: Dado Feigenblatt <dado(at)wildbrain(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Cc: josh(at)agliodbs(dot)com
Subject: Re: indexing of hierarchical data
Date: 2001-07-07 00:20:36
Message-ID: 3B4655D4.465A52F4@wildbrain.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

<!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(at)wildbrain(dot)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>

Attachment Content-Type Size
unknown_filename text/html 4.9 KB

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Carlo Vitolo 2001-07-07 11:05:59 Problem with function & trigger
Previous Message Josh Berkus 2001-07-06 23:59:42 Re: indexing of hierarchical data