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

Groups > comp.databases.postgresql > #427 > unrolled thread

Best way to encode a constraint across tables

Back to article view | Back to comp.databases.postgresql

Contents

  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

#427 — Best way to encode a constraint across tables

Hey all,

I am a little rusty on my SQL and Database design. I've been working to 
get a basic appplication back up and running, but I've run into a 
constraint that I suspect is rather common, but which I do not know the 
right solution to.

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?

-- 
Aaron W. Hsu | arcfide@sacrideo.us | http://www.sacrideo.us
לֵ֤ב חֲכָמִים֙ בְּבֵ֣ית אֵ֔בֶל וְלֵ֥ב כְּסִילִ֖ים בְּבֵ֥ית שִׂמְחָֽה׃

[toc] | [next] | [standalone]

#428

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-

[toc] | [prev] | [next] | [standalone]

#429

On Sat, 12 Jan 2013 12:53:51 +0000, Matthew Woodcraft wrote:

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

This doesn't quite work because I want multiple assignments to be able to 
use the same problem. However, your comment brought the obvious to my 
attention. I already have such a table contains(assignmentid, problemid) 
for just such a purpose. What was missing was that I was using something 
like commentson(submissionid, problemid, ...) for my commentson relation, 
but that's actually not helpful here, and submissionid is actually an 
artificial key anyways. In reality, the natural key is (assignment, 
owner, date), and if I used that instead, to get commentson(assignment, 
owner, date, problemid, ...) then it's trivial to define a constraint on 
the contains relation using problemid and assignment. 

In fact, I realized that I had thrown in artificial "id" keys without any 
real need to have them, so I have since removed them from my schema and 
things have just fallen out to work correctly. :-)

Thanks for the help!

-- 
Aaron W. Hsu | arcfide@sacrideo.us | http://www.sacrideo.us
לֵ֤ב חֲכָמִים֙ בְּבֵ֣ית אֵ֔בֶל וְלֵ֥ב כְּסִילִ֖ים בְּבֵ֥ית שִׂמְחָֽה׃

[toc] | [prev] | [next] | [standalone]

#430

On 2013-01-12, Aaron W. Hsu <arcfide@sacrideo.us> wrote:

> 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.

How is the assignment containing a problem represented in the database?

-- 
⚂⚃ 100% natural

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web