Re: indexing of hierarchical data

Josh Berkus wrote: <blockquote type="CITE">Dado, <p>        Yeah, me again.  What can I say?  I'm procrastinating, and
liststuff <br />is a great way to do it.  And I prefer design theory issues. <p>project    sequence         shot
<p>CopFilm1  alley shooting   death of the bad guy <br />CopFilm2   car chase        death of the bad guy <br
/>CopFilm3  car chase        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>       This sounds fine so far. <br />  <p>So if I wanted to select CopFilm3, car chase, death of the bad guy <br
/>Ihad to find the ID of the project CopFilm3, the ID of the sequence car <br />chase belonging to that project and
thenshot 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,
soI don't have to perform any joins. <p>        What's wrong with joins? <p>Also, projects and sequences have
alphabeticalcodes assigned to them, <br />which is usually the prefered way of accessing the data. <br />So, it is my
impressionthat I should store those codes in the shot as <br />foreign keys with on update cascade <br />should someone
decideto rename projects and sequences, and their codes, <br />which happens. <br />Is this approach ok or should I
stickto serial ID's and make the <br />lookups? <br />Any comment on problems like this? <p>        Yes.  You want to
haveas primary and foriegn keys values that do not <br />change over the lifetime of the record.  Any time that you
choose<br />instead user-modifiable records you are introducing a world of headaches <br />and trigger maintainence.
<p>       The solution to the above design problem is simple: <p>table projects <br />        project_ID SERIAL PRIMARY
KEY<br />        project_code VARCHAR(5) NOT NULL <br />        project_name VARCHAR(100) NOT NULL <p>table sequences
<br/>        sequence_ID SERIAL PRIMARY KEY <br />        project_ID INT NOT NULL REFERENCES projects(project_ID) <br
/>       sequence_code VARCHAR(5) NOT NULL <br />        sequence_name VARCHAR(100) NOT NULL <p>table shots <br
/>       shot_ID SERIAL PRIMARY KEY <br />        sequence_ID INT NOT NULL REFERENCES sequences(sequence_ID) <br
/>       shot_code VARCHAR(5) NOT NULL <br />        shot_name VARCHAR(100) NOT NULL <p>CREATE VIEW vw_shots AS <br
/>SELECTproject_code, project_name, sequence_code, sequence_name, <br />        shot_code, shot_name <br />FROM
projectsJOIN sequences USING (project_ID) <br />        JOIN shots USING (sequence_ID); <p>Then you users can access
thisview, and search by codes without being <br />aware that the numerical ID's even exist.  For that matter, you can
<br/>impose UNIQUE constraints on codes within their context without tying up <br />those codes for all time or
preventingyour users from changing the <br />codes.</blockquote> I think that's where experience comes in, uh? <br />I
haven'tthought of creating a view that way. <br />That's another very helpfull hint. <br />I think if we don't get a
consultingbudget anytime soon I'll have to get you dinner ;) <p>Thanks. <p>PS: can't your e-mail client insert >'s
onreplies? It's kind of heard to sift through. <pre>-- 
 
Dado Feigenblatt                                 Wild Brain, Inc.   
Technical Director                               (415) 553-8000 x???
dado@wildbrain.com                               San Francisco, CA.</pre>