Re: Storing number '001' ?
От | Josh Berkus |
---|---|
Тема | Re: Storing number '001' ? |
Дата | |
Msg-id | web-524943@davinci.ethosmedia.com обсуждение исходный текст |
Ответ на | Storing number '001' ? (Charles Hauser <chauser@acpub.duke.edu>) |
Список | pgsql-novice |
Chuck, > I have set up TABLES: clone_fasta, clone_qual and gb_accessions. All > three tables have the same PRIMARY KEY > (project,plate,p_row,p_column,read,ver) but contain other unique data > (seq and length in the case of TABLE clone_fasta shown below). If I were setting up this database, I would create a "surrogate key" using a SERIAL column and use that as my key for joins. While your "real" primary key is the 6-column combination above, I think you will find 6-column joins a royal pain in practice. In other words, change the schema per my comments below. > CREATE TABLE clone_fasta( clone_id SERIAL NOT NULL, > project INTEGER NOT NULL, > plate INTEGER NOT NULL, <-- to_char(plate, '000') > p_row CHAR(1) NOT NULL, > p_column INTEGER NOT NULL, <-- to_char(p_column, '00') > read CHAR(1) NOT NULL, > ver INTEGER NOT NULL, > length INTEGER NOT NULL, > seq TEXT NOT NULL, > PRIMARY KEY (project,plate,p_row,p_column,read,ver), CONSTRAINT clone_id_cs UNIQUE (clone_id) > ); If there are other unique/constrained columns, don't forget to add constraints for them as well. Finally, should any of the columns above link to reference tables of possible values? For example, I'm willing to bet that the "read" column is constrained to 2 or 3 values. And finally, you'll want the primary key and all columns with constraints, or columns which regularly JOIN, indexed. Pardon me if you already know all this. > $result = $conn->exec( > "SELECT > clone_fasta.seq,clone_fasta.length,clone_qual.qual,library.details,gb_accessions.accn_no > FROM clone_fasta,clone_qual,gb_accessions,library > WHERE clone_fasta.project = library.project AND > $la1 = $la2 AND # set gb_accessions PK = clone_fasta PK > $la3 = $la2 AND # set clone_qual PK = clone_fasta PK > clone_fasta.project = '$estIDs[0]' AND > clone_fasta.plate = '$estIDs[1]' AND > clone_fasta.p_row = '$estIDs[2]' AND > clone_fasta.p_column = '$estIDs[3]' AND > clone_fasta.read = '$estIDs[4]' AND > clone_fasta.ver = '$estIDs[5]' > "); > > > I tried to use: clone_fasta.to_char(plate,'000') = '$estIDs[1]' , > but this errors on syntax. First, is that an interface language above that will swap out the $estIDs[] values that you have listed? PHP, maybe? Second, yes, you did make a slight mistake in your syntax: to_char(clone_fasta.plate,'000') = '$estIDs[1]' Clearer, now? BTW, I think that you're doing OK in the database design, but would benefit significantly from a good database design book. See http://techdocs.postgresql.org/bookreviews.php -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 по дате отправления: