From: Charles H. <ch...@du...> - 2002-10-21 19:07:38
|
Hi David, First, great job on the schema! Leave it to me to look at the inconsequential first. I had been contemplating a similar 'pub' module for our Chlamy project to get my feet wet, and wanted to offer a couple of suggestions/alternatives derived from looking at the schema for TAIR and elsewhere. As part of TABLE pub you might consider breaking out the recurring 'details' into separate TABLES (e.g. pub_source(details for each journal, book etc.) Similar considerations for TABLE author to store Institutions, address & phone numbers. create table pub ( pub_id serial not null, primary key (pub_id), ## title of paper, chapter of book, journal, etc title text, ## title of part if one of a series volumetitle text, volume varchar(255), ## full name of (journal) series series_name varchar(255), issue varchar(255), pyear varchar(255), ## page number range[s], eg, 457--459, viii + 664pp, lv--lvii pages varchar(255), ## the type of the publication (book, journal, poem, graffiti, etc) type_id int not null, foreign key (type_id) references cvterm (cvterm_id), ## do we want this even though we have the relationship in pub_relationship? is_obsolete boolean default 'false', publisher varchar(255), pubplace varchar(255), timeentered timestamp not null default current_timestamp, timelastmod timestamp not null default current_timestamp ); CREATE TABLE publication_type ( type VARCHAR(20) NOT NULL PRIMARY KEY ); INSERT INTO publication_type VALUES ( 'abstract' ); INSERT INTO publication_type VALUES ( 'review' ); INSERT INTO publication_type VALUES ( 'research_article' ); INSERT INTO publication_type VALUES ( 'book' ); INSERT INTO publication_type VALUES ( 'press_article' ); INSERT INTO publication_type VALUES ( 'book_chapter' ); INSERT INTO publication_type VALUES ( 'dissertation' ); INSERT INTO publication_type VALUES ( 'unknown' ); CREATE TABLE pub_source_type ( type VARCHAR(25) NOT NULL PRIMARY KEY ); INSERT INTO pub_source_type VALUES ( 'journal' ); INSERT INTO pub_source_type VALUES ( 'book' ); INSERT INTO pub_source_type VALUES ( 'book_series' ); INSERT INTO pub_source_type VALUES ( 'conference_proceedings' ); INSERT INTO pub_source_type VALUES ( 'dissertation' ); INSERT INTO pub_source_type VALUES ( 'ais' ); INSERT INTO pub_source_type VALUES ( 'weeds_world' ); INSERT INTO pub_source_type VALUES ( 'electronic_reference' ); INSERT INTO pub_source_type VALUES ( 'unknown' ); INSERT INTO pub_source_type VALUES ( 'press' ); CREATE TABLE Publication ( publication_id SERIAL PRIMARY KEY, pub_source_id numeric(12) NOT NULL, ------> Journal/book details type varchar(10) NOT NULL REFERENCES publication_type(type) -----> reference TABLE; restrict data input medline_id char(10) NULL, is_print_reference boolean, is_electronic_reference boolean, .....others...... ); CREATE UNIQUE INDEX publicationindex1 ON publication ( pub_id ); CREATE TABLE pub_source ( pub_source_id SERIAL PRIMARY KEY, name varchar(255) NOT NULL, type varchar(10) NOT NULL REFERENCES pub_source_type(type) -----> reference TABLE; restrict data input publisher varchar(100) NULL, isbn_number varchar(13) NULL, editors varchar(255) NULL, issn_number varchar(10) NULL, pub_id Identifier_type NULL ); CREATE INDEX pub_sourceindex1 ON pub_source ( pub_id ); |