Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #428

Re: Best way to encode a constraint across tables

Show all headers | View raw

Aaron W. Hsu <arcfide@sacrideo.us> wrote:
> I am modeling assignments, submissions to assignments, and comments on 
> submissions. The trick that I am running into is that a submission is 
> submitted for a specific assignment, and an assignment contains a set of 
> problems, while one comments on a specific submission and a specific 
> problem in the submission.

> Right now my commentson table has a column for the submission and a 
> column for the problem, both with foreign key constraints. However, puting 
> a constraint on the problems table for the problem column isn't good 
> enough, because I also want to constrain the problem to being one of the 
> problems that the assignment for which the submission was submitted for 
> contains.

> One option I found for doing this is using a view SubmissionsProblems 
> that gives the problems for a given submission, and then using a foreign 
> key constraint on that, but is that what I want to do, or is there 
> another way to do this?

One other way is to put the assignment's identifier explicitly into the
problems table.

If you give the submissions and problems tables composite primary keys
which include the assignment id, then it comes out quite naturally.
Perhaps something like this:

assignments table:
 *assignment_id
  ...

problems table:
 *assignment_id
 *problem_id
  ...

submissions table:
 *assignment_id
 *submission_id
  ...

comments table:
 *comment_id
  assignment_id
  problem_id
  submission_id
  ...

  FOREIGN KEY (assignment_id, problem_id) REFERENCES problems
  FOREIGN KEY (assignment_id, submission_id) REFERENCES submissions

(where * indicates the columns in the table's primary key)

-M-

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Best way to encode a constraint across tables "Aaron W. Hsu" <arcfide@sacrideo.us> - 2013-01-11 19:05 -0600
  Re: Best way to encode a constraint across tables mattheww@chiark.greenend.org.uk (Matthew Woodcraft) - 2013-01-12 12:53 +0000
    Re: Best way to encode a constraint across tables "Aaron W. Hsu" <arcfide@sacrideo.us> - 2013-01-12 13:04 -0600
  Re: Best way to encode a constraint across tables Jasen Betts <jasen@xnet.co.nz> - 2013-01-13 07:04 +0000

csiph-web