Re: Modelling Problem

>> As you can see a project can have a proposal related to it. Now I have a Comment table that is self referencing (representing a threaded discussion group). <<

Is this a hierarchy of proposals within only one project? It would seem to be, so the projects table would be like this:

CREATE TABLE Projects
(project_id INTEGER NOT NULL,
 proposal_id INTEGER NOT NULL
   REFERENCES Proposals(proposal_id),
 PRIMARY KEY (project_id, proposal_id)
 .. ) ;

CREATE TABLE Proposals
(proposal_id INTEGER NOT NULL PRIMARY KEY,  ..),

>> How can I design this in such a way that the comment can relate to either a project or a proposal? <<

CREATE TABLE ProjectComments
(project_id INTEGER NOT NULL,
 proposal_id INTEGER NOT NULL,
   REFERENCES Projects (project_id, proposal_id), comment_seq INTEGER DEFAULT 0 NOT NULL
    CHECK (comment_seq >= 0),

• seq = 0 means applies to project level PRIMARY KEY(project_id, proposal_id, comment_seq) comment_txt VARCHAR(8000) NOT NULL, ..),