0

I have a situation where I have three tables: user, assignment and test. A user must have completed an assignment before he can take the test. This means the test table has both both a user foreign key and an assignment foreign key on it.

I could write a sql statement like this: insert into test (name, user_id, assignment_id) values ("final exam", 1, 1) which would check to see if the user and assignment exist before doing the insert. However it would not check to see if the user and assignment were related.

The easy way to solve this problem is to do a separate query before the insert to ensure the user has an assignment. I'm wondering if I can accomplish both in one query though. I'm not all that experienced with constraints or subqueries, both of which could be solutions. Looking for a best practice here as it will be used throughout an application.

2
  • If you have a many-to-many relationship, you need a linking table. Please post your CREATE TABLE statements to help out. Sep 17, 2020 at 23:43
  • "assignment" implies both an entity and a relationship". I'll guess that you are missing the relationship part. Just like a class can exist without students and students enroll in a class - the enrollment is an entity that forms the relationship between student and class. In this case one can think of your model as "objectives exist, a user is assigned to an objective, when the objective is completed, that user is tested on that assignment.
    – SMor
    Sep 18, 2020 at 0:10

1 Answer 1

0

Recommended scheme:

Existing tables (PK == Primary key):

user (user_id PK, etc.)
assignment (assignment_id PK, etc.) 
test (test_id PK, etc.)

Relations tables (PK == Primary key, FK == Foreign key):

user_assignment (user_assignment_id PK, user_id FK, assignment_id FK, etc.)

This table creates the relation "user to assignment" which would check to see if the user and assignment exist.

user_test (user_test_id PK, user_assignment_id FK, test_id FK, etc.)

This table creates the relation "user with existing assignment to test".

3
  • how would you compare your solution to something like this: insert into test (name, student_id, assignment_id) select ‘final exam’, assignments.student_id, assignments.id from assignments where assignments.id = 2 AND assignments.student_id = 1; ? Sep 18, 2020 at 17:09
  • @BradenHolt And where is the integrity check of user=>assignment relation existence? But no, I'll ask you something else. Imagine that the user have already received the assignment, but have no taken the test yet - where the presence of assignment for this user is stored in this moment?
    – Akina
    Sep 18, 2020 at 17:26
  • ahh replace student_id with user_id in the where clause Sep 18, 2020 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.