Re: indexing of hierarchical data - Mailing list pgsql-sql
From | Dado Feigenblatt |
---|---|
Subject | Re: indexing of hierarchical data |
Date | |
Msg-id | 3B4655D4.465A52F4@wildbrain.com Whole thread Raw |
In response to | Re: indexing of hierarchical data ("Josh Berkus" <josh@agliodbs.com>) |
List | pgsql-sql |
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>