Re: Storing number '001' ?
От | Josh Berkus |
---|---|
Тема | Re: Storing number '001' ? |
Дата | |
Msg-id | web-532082@davinci.ethosmedia.com обсуждение исходный текст |
Ответ на | Storing number '001' ? (Charles Hauser <chauser@acpub.duke.edu>) |
Список | pgsql-novice |
Chuck, > I am considering one last relational table to relate contig::blast. > > These two tables are related as: > a contig has 0,1 or more blast hits (0 only if contig not yet run > thru blast) > a blast hit exists for 0, 1 or more contigs (0 only when no data > loaded) > > As I have it currently, TABLE blast contains the FK contig_id to > track which contig the blast record refers to. Yes, but it doesn't fit your statements above, which clearly imply a many-to-many relationship. Drop the FK. > > Is it appropriate in this case to make a TABLE contig_blast, to me it > appears so....? This is correct. However, your SQL is wrong. See my corrections below. > Now that I think about it, it may also be useful to make a table > clone_blast. If one wanted to do a query for which blast results > pertained to a given clone. > > clone --- (assembled into) ---> contig ---> blast result > | | > ------------------------------------------------- This, on the other hand, is unnecessary and even problematic. We already have the table clone_contig. If we add the relationship table contig_blast, then we can select all clones in a blast by selecting: blast JOIN contig_blast JOIN contigs JOIN clone_contigs JOIN clones Adding a seperate relationship table to maintain this information would be redundant, and also troublesome because eventually clone_blast would be bound to get out of synch with the relationship above. > CREATE TABLE contig ( > contig_id SERIAL PRIMARY KEY, > assembly_date date NOT NULL, > contig_no integer NOT NULL, > ver integer NOT NULL, > length INTEGER NOT NULL, > seq TEXT NOT NULL, > UNIQUE (assembly_date,contig_no,ver) > ); > > > CREATE TABLE blast ( > blast_id SERIAL PRIMARY KEY, ###> contig_id INTEGER REFERENCES contig(contig_id) ON DELETE CASCADE,### Delete this column. It does not fit the data model. > score integer, > homolog text > ); > > CREATE TABLE contig_blast ( > blast_id INTEGER REFERENCES blast(blast_id) ON DELETE CASCADE, > contig_id INTEGER REFERENCES contig(contig_id) ON DELETE CASCADE, > UNIQUE(clone_id,contig_id) uh-uh: UNIQUE (blast_id, contig_id) > ); > CREATE TABLE clone_blast ( > blast_id INTEGER REFERENCES blast(blast_id) ON DELETE CASCADE, > clone_id INTEGER REFERENCES clone(clone_id) ON DELETE CASCADE, > UNIQUE(clone_id,contig_id) > ); Drop this table. -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
В списке pgsql-novice по дате отправления: