From dado@wildbrain.com Fri Jul 6 23:17:16 2001 Received: from flip.wildbrain.com ([206.54.55.32]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f66NHGa24110 for ; Fri, 6 Jul 2001 19:17:16 -0400 (EDT) (envelope-from dado@wildbrain.com) Received: from wildbrain.com (daedelus.wildbrain.com [192.168.1.38]) by flip.wildbrain.com (8.9.3/8.9.3) with ESMTP id QAA11665 for ; Fri, 6 Jul 2001 16:32:16 -0700 Message-ID: <3B4646A2.9E02268B@wildbrain.com> Date: Fri, 06 Jul 2001 16:15:46 -0700 From: Dado Feigenblatt X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.4.2-2smp i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: indexing of hierarchical data Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200107/77 X-Sequence-Number: 3861 We make cartoons here.
But let's say we were working on 3 different Cop movies.
Our projects are devided into project, sequence, and shot.

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.

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.
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?

Thanks.

-- 
Dado Feigenblatt                                 Wild Brain, Inc.   
Technical Director                               (415) 553-8000 x???
dado@wildbrain.com                               San Francisco, CA.
  From josh@agliodbs.com Fri Jul 6 23:59:46 2001 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f66Nxka36197 for ; Fri, 6 Jul 2001 19:59:46 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 82025; Fri, 06 Jul 2001 16:59:42 -0700 From: "Josh Berkus" Subject: Re: indexing of hierarchical data To: Dado Feigenblatt , pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Fri, 06 Jul 2001 16:59:42 -0700 Message-ID: In-Reply-To: <3B4646A2.9E02268B@wildbrain.com> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200107/78 X-Sequence-Number: 3862 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 From dado@wildbrain.com Sat Jul 7 00:22:07 2001 Received: from flip.wildbrain.com ([206.54.55.32]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f670M7a43385 for ; Fri, 6 Jul 2001 20:22:07 -0400 (EDT) (envelope-from dado@wildbrain.com) Received: from wildbrain.com (daedelus.wildbrain.com [192.168.1.38]) by flip.wildbrain.com (8.9.3/8.9.3) with ESMTP id RAA11887; Fri, 6 Jul 2001 17:37:07 -0700 Message-ID: <3B4655D4.465A52F4@wildbrain.com> Date: Fri, 06 Jul 2001 17:20:36 -0700 From: Dado Feigenblatt X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.4.2-2smp i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org CC: josh@agliodbs.com Subject: Re: indexing of hierarchical data References: Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200107/79 X-Sequence-Number: 3863 Josh Berkus wrote:
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.

I think that's where experience comes in, uh?
I haven't thought of creating a view that way.
That's another very helpfull hint.
I think if we don't get a consulting budget anytime soon I'll have to get you dinner ;)

Thanks.

PS: can't your e-mail client insert >'s on replies? It's kind of heard to sift through.

-- 
Dado Feigenblatt                                 Wild Brain, Inc.   
Technical Director                               (415) 553-8000 x???
dado@wildbrain.com                               San Francisco, CA.