<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
Josh Berkus wrote:
<blockquote TYPE=CITE>Dado,
<p> Yeah, me again. What
can I say? I'm procrastinating, and list stuff
<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>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> 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> Yes. You want to have
as 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>SELECT project_code, project_name, sequence_code, sequence_name,
<br> shot_code, shot_name
<br>FROM projects JOIN sequences USING (project_ID)
<br> 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. 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>--
Dado Feigenblatt Wild Brain, Inc.
Technical Director (415) 553-8000 x???
dado(at)wildbrain(dot)com San Francisco, CA.</pre>
</html>