pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
indexing of hierarchical data
3+ messages / 2 participants
[nested] [flat]

* indexing of hierarchical data
@ 2001-07-06 23:15  Dado Feigenblatt <dado@wildbrain.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Dado Feigenblatt @ 2001-07-06 23:15 UTC (permalink / raw)
  To: pgsql-sql

<!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&nbsp;&nbsp;&nbsp; sequence&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
shot</tt><tt></tt>
<p><tt>CopFilm1&nbsp;&nbsp; alley shooting&nbsp;&nbsp; death of the bad
guy</tt>
<br><tt>CopFilm2&nbsp;&nbsp; car chase&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
death of the bad guy</tt>
<br><tt>CopFilm3&nbsp;&nbsp; car chase&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
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&nbsp;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&nbsp;of the project <tt>CopFilm3</tt>, the ID&nbsp;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>--&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>




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: indexing of hierarchical data
@ 2001-07-06 23:59  Josh Berkus <josh@agliodbs.com>
  parent: Dado Feigenblatt <dado@wildbrain.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Josh Berkus @ 2001-07-06 23:59 UTC (permalink / raw)
  To: Dado Feigenblatt <dado@wildbrain.com>; pgsql-sql

Dado,

	Yeah, me again.  What can I say?  I'm procrastinating, and list stuff
is a great way to do it.  And I prefer design theory issues.


project    sequence         shot 

CopFilm1   alley shooting   death of the bad guy 
CopFilm2   car chase        death of the bad guy 
CopFilm3   car chase        death of the bad guy 

At first I was indexing the shots just buy shot_ID (serial), and storing
the sequence_ID it belongs to. 
On the sequence record, I was storing the project_ID it belongs to.

	This sounds fine so far.
 

So if I wanted to select CopFilm3, car chase, death of the bad guy 
I had to find the ID of the project CopFilm3, the ID of the sequence car
chase belonging to that project and then shot death of the bad guy
belonging to that sequence. 
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. 

	What's wrong with joins?  

Also, projects and sequences have alphabetical codes assigned to them,
which is usually the prefered way of accessing the data. 
So, it is my impression that I should store those codes in the shot as
foreign keys with on update cascade 
should someone decide to rename projects and sequences, and their codes,
which happens. 
Is this approach ok or should I stick to serial ID's and make the
lookups? 
Any comment on problems like this? 

	Yes.  You want to have as primary and foriegn keys values that do not
change over the lifetime of the record.  Any time that you choose
instead user-modifiable records you are introducing a world of headaches
and trigger maintainence.

	The solution to the above design problem is simple:

table projects
	project_ID SERIAL PRIMARY KEY
	project_code VARCHAR(5) NOT NULL
	project_name VARCHAR(100) NOT NULL

table sequences
	sequence_ID SERIAL PRIMARY KEY
	project_ID INT NOT NULL REFERENCES projects(project_ID)
	sequence_code VARCHAR(5) NOT NULL
	sequence_name VARCHAR(100) NOT NULL

table shots
	shot_ID SERIAL PRIMARY KEY
	sequence_ID INT NOT NULL REFERENCES sequences(sequence_ID)
	shot_code VARCHAR(5) NOT NULL
	shot_name VARCHAR(100) NOT NULL

CREATE VIEW vw_shots AS
SELECT project_code, project_name, sequence_code, sequence_name, 
	shot_code, shot_name
FROM projects JOIN sequences USING (project_ID)
	JOIN shots USING (sequence_ID);

Then you users can access this view, and search by codes without being
aware that the numerical ID's even exist.  For that matter, you can
impose UNIQUE constraints on codes within their context without tying up
those codes for all time or preventing your users from changing the
codes.

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: indexing of hierarchical data
@ 2001-07-07 00:20  Dado Feigenblatt <dado@wildbrain.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Dado Feigenblatt @ 2001-07-07 00:20 UTC (permalink / raw)
  To: pgsql-sql; +Cc: josh@agliodbs.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>




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-07-07 00:20 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-06 23:15 indexing of hierarchical data Dado Feigenblatt <dado@wildbrain.com>
2001-07-06 23:59 ` Josh Berkus <josh@agliodbs.com>
2001-07-07 00:20   ` Dado Feigenblatt <dado@wildbrain.com>

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