MySQL Insert Query With Conditional Statement

Question

Im creating a MySQL Schema with Two Tables (Student and Instructor).

1. Students are given a student number when they join the school.
   This is stored along with their name, date of birth, and the date they joined
   the school.

2. All instructors are also students, but clearly, not all students are
   instructors. In addition to the normal student information, for all 
   instructors, the date that they start working as an instructor must be
   recorded, along with their instructor status (compensated or volunteer).

My Schema:

STUDENT

+---------------+
| ID            | PK
+---------------+
| LNAME         |
+---------------+
| FNAME         |
+---------------+
| MNAME         |
+---------------+
| BDAY          |
+---------------+
| JOIN_DATE     |
+---------------+
| IS_INSTRUCTOR |
+---------------+

INSTRUCTOR

+---------------+
| ID            | PK
+---------------+
| STUDENT_ID    | FK
+---------------+
| INSTR_STATUS  | (compensated or volunteer)
+---------------+
| DATE_WORKED   |
+---------------+

How can when I insert into STUDENT TABLE and if IS_INSTRUCTOR = 1 (meaning true) INSTRUCTOR TABLE (STUDENT_ID) will have the STUDENT.ID?

Or you guy have a better Table Design? TIA

I am not sure why you need `is_instructor` field in the `student` table. You will need to update it each time you add or remove a record from `instructor` table. If you need to see if particular student is an instructor a simple hash join on `student_id` would suffice. But the options I see: first is using a stored procedure, and the second option is just to wrap separate inserts into one transaction. — Alex P, Mar 14 '14 at 20:52
The diagram almost remains the same. I would remove calculated field `is_instructor` unless there are specific application requirements which I don't know. And also I would remove surrogate primary key `id` from `instructor` table and make `student_id` a primary key which is also a foreign key to `student` table. — Alex P, Mar 14 '14 at 21:14
@AlexP instead of comment why not answer so I can vote or accept it? — Just A Noob, Mar 14 '14 at 21:32

score 1 · Accepted Answer · answered Mar 14 '14 at 21:49

I am not sure why you need is_instructor field in the student table. You will need to update it each time you add or remove a record from instructor table. If you need to see if particular student is an instructor a simple hash join on student_id would suffice. But the options I see: first is using a stored procedure, and the second option is just to wrap separate inserts into one transaction.

I would remove calculated field is_instructor unless there are specific application requirements which I don't know. Though surrogate (or artificial) keys can be useful, for current design I would remove primary key id from instructor table and make student_id a primary key which is also a foreign key to student table.

score 0 · Answer 2 · answered Mar 14 '14 at 20:08

0

How about something in the lines of...

INSERT INTO INSTRUCTOR (STUDENT_ID, INSTR_STATUS, DATE_WORKED)
SELECT ID, 'volunteer', NOW() 
FROM STUDENT
WHERE IS_INSTRUCTOR = 1

You might want to rethink your design.

answered Mar 14 '14 at 20:08

majidarif

18,694
16
88
133

score 0 · Answer 3 · edited Mar 14 '14 at 20:12

0

I think a trigger is the best think you can do at this moment. But the model is a little bit confuse. Ins't better to create just one table for everything?

+---------------+  
| ID            | PK  
+---------------+  
| LNAME         |  
+---------------+  
| FNAME         |  
+---------------+  
| MNAME         |  
+---------------+  
| BDAY          |  
+---------------+  
| JOIN_DATE     |  
+---------------+  
| IS_INSTRUCTOR |  
+---------------+  
| INSTR_STATUS  | (compensated or volunteer)  
+---------------+  
| DATE_WORKED   |  
+---------------+

edited Mar 14 '14 at 20:12

majidarif

18,694
16
88
133

answered Mar 14 '14 at 20:10

Sornii

421
3
11

1

You will have lots of nulls with this representation. – Alex P Mar 14 '14 at 20:13

score 0 · Answer 4 · edited May 23 '17 at 11:57

0

This link may help you

You would use a WHERE clause like:

WHERE IS_INSTRUCTOR = 1

edited May 23 '17 at 11:57

Community

1
1

answered Mar 14 '14 at 20:11

Sohail xIN3N

2,951
2
30
29

MySQL Insert Query With Conditional Statement

4 Answers4