From: Dado Feigenblatt <dado@wildbrain.com>
To: pgsql-sql@postgresql.org
Subject: indexing of hierarchical data
Date: Fri, 06 Jul 2001 16:15:46 -0700
Message-ID: <3B4646A2.9E02268B@wildbrain.com> (raw)
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
We make cartoons here.
<br>But let's say we were working on 3 different Cop movies.
<br>Our projects are devided into project, sequence, and shot.
<p><tt>project sequence
shot</tt><tt></tt>
<p><tt>CopFilm1 alley shooting death of the bad
guy</tt>
<br><tt>CopFilm2 car chase
death of the bad guy</tt>
<br><tt>CopFilm3 car chase
death of the bad guy</tt><tt></tt>
<p>At first I was indexing the shots just buy shot_ID (serial), and storing
the sequence_ID it belongs to.
<br>On the sequence record, I was storing the project_ID it belongs
to.
<p>So if I wanted to select <tt>CopFilm3, car chase, death of the bad guy</tt>
<br>I had to find the ID of the project <tt>CopFilm3</tt>, the ID of
the sequence <tt>car chase</tt> belonging to that project and then shot
<tt>death of the bad guy </tt>belonging to that sequence.
<br>As most of the operations happen at the shot level, for performance
reasons I think it might be better to store the project and sequence with
the shot, so I don't have to perform any joins.
<br>Also, projects and sequences have alphabetical codes assigned to them,
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 foreign keys with <tt>on update cascade</tt>
<br>should someone decide to rename projects and sequences, and their codes,
which happens.
<br>Is this approach ok or should I stick to serial ID's and make the lookups?
<br>Any comment on problems like this?
<p>Thanks.
<pre>--
Dado Feigenblatt Wild Brain, Inc.
Technical Director (415) 553-8000 x???
dado@wildbrain.com San Francisco, CA.</pre>
</html>
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
Subject: Re: indexing of hierarchical data
In-Reply-To: <3B4646A2.9E02268B@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