A generic schema for the PcarnBase Application

PcarnBase.sql

CREATE TABLE NCBI_Annotation
(
  UnigeneID   VARCHAR(32) PRIMARY KEY NOT NULL,
  Description VARCHAR(255)            NOT NULL,
  ProteinsPID VARCHAR(32)             NOT NULL
);

CREATE TABLE Proteins
(
  PID       VARCHAR(32) PRIMARY KEY NOT NULL,
  Sequence  VARCHAR(65535)          NOT NULL,
  UnigeneID VARCHAR(32)
);

ALTER TABLE Proteins
  ADD CONSTRAINT fk_protein_UnigeneId
FOREIGN KEY (UnigeneID) REFERENCES NCBI_Annotation (UnigeneID);

CREATE TABLE DNAs
(
  DID       VARCHAR(32) PRIMARY KEY NOT NULL,
  Sequence  VARCHAR(65535)          NOT NULL,
  UnigeneID VARCHAR(32)
);

ALTER TABLE DNAs
  ADD CONSTRAINT fk_dnas_UnigeneId
FOREIGN KEY (UnigeneID) REFERENCES NCBI_Annotation (UnigeneID);

CREATE TABLE Gene_Ontology
(
  GOclassID VARCHAR(32) PRIMARY KEY NOT NULL,
  Ontology  VARCHAR(255)            NOT NULL
);

CREATE TABLE NCBI_GO_Relation
(
  NG_rid    INTEGER PRIMARY KEY NOT NULL,
  UnigeneID VARCHAR(32)         NOT NULL,
  GOclassID VARCHAR(32)         NOT NULL
);

ALTER TABLE NCBI_GO_Relation
  ADD CONSTRAINT fk_ncbi_go_UnigeneID
FOREIGN KEY (UnigeneID) REFERENCES NCBI_Annotation (UnigeneID);

ALTER TABLE NCBI_GO_Relation
  ADD CONSTRAINT fk_ncbi_go_GOclassID
FOREIGN KEY (GOclassID) REFERENCES Gene_Ontology (GOclassID);

CREATE TABLE KEGG
(
  Pathway VARCHAR(32) PRIMARY KEY NOT NULL,
  PathID  VARCHAR(255)            NOT NULL
);

CREATE TABLE NCBI_KEGG_Relation
(
  NK_rid    INTEGER PRIMARY KEY NOT NULL,
  UnigeneID VARCHAR(32)         NOT NULL,
  Pathway   VARCHAR(32)         NOT NULL
);

ALTER TABLE NCBI_KEGG_Relation
  ADD CONSTRAINT fk_ncbi_kegg_UnigeneID
FOREIGN KEY (UnigeneID) REFERENCES NCBI_Annotation (UnigeneID);

ALTER TABLE NCBI_KEGG_Relation
  ADD CONSTRAINT fk_ncbi_kegg_pathway
FOREIGN KEY (Pathway) REFERENCES KEGG (Pathway);

Interesting note: when myphpadmin processes the sequence rows for both the DNAs and Proteins tables, it returns error #1118; saying that the row size (65535) is too large for the data type (varchar), and suggests changing the type to either text or BLOB. Currently, these rows are of the text type for me.