Sql-server – Ensuring relationship between child tables exists prior to SQL insert

MySQLpostgresqlsql server

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.

Best Answer

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

Related Solutions

Mysql – Bit column vs multiple join tables

I'd have role table that contains "student", "instructor", etc. Then join table would look like user_role(user_id,role_id, fos_id, date_from, date_thru) . Date_from and date_thru may or may not make sense for your task....

Mysql – Table Design Cardinality

--this is postgres syntax, but similar for mysql
--using single table inheritance for simplicity

--organizations (o) and individuals (i) go here:

create table parties (
  party_id int primary key,
  type char(1) not null check (type in ('i', 'o') ), --use a lookup table in real life
  full_legal_name varchar(255) not null
);

--parties that play the role of vendor (v) or buyer (b) go here. parties can play many roles:

create table party_roles (
  party_id int references parties(party_id),
  type char(1) check ( type in ('v', 'b') ), --use a lookup table in real life

  --add extra role info here

  primary key (party_id, type)
);

--sales orders go here. foreign keys ensure vendor is a vendor role, etc

create table sales_orders (
  order_id int primary key,
  order_date timestamptz not null default current_timestamp,
  vendor_party_id int not null,
  vendor_type char(1) not null default 'v',
  buyer_party_id int not null,
  buyer_type char(1) not null default 'b',

  foreign key (vendor_party_id, vendor_type) references party_roles(party_id, type),

  foreign key (buyer_party_id, buyer_type) references party_roles(party_id, type)
);

--create a company that plays the role of a vendor:
begin;
insert into parties values (1, 'o', 'Acme, Inc.');
insert into party_roles values (1, 'v'); 
commit;    

--create a company that plays the role of a buyer:
begin;
insert into parties values (2, 'o', 'Globalcorp.biz');
insert into party_roles values (2, 'b');
commit;

--create a sales order, vendor is acme, buyer is globalcorp:
insert into sales_orders (order_id, vendor_party_id, buyer_party_id) values (1, 1, 2);

Fiddle with it here: http://sqlfiddle.com/#!15/a76fd/2

Best Answer

Related Solutions

Mysql – Bit column vs multiple join tables

Mysql – Table Design Cardinality

Related Question