CODEWITHSUNDEEP
mysql
Bob Machine
Bob Machine

Reputation: 141

mysql create table based on columns from another table and auto updated

I could have the wording 'wrong' here (new to mysql) but i hope i've explained what I'm trying to do well.

i have a table called submissions with 4 fields submitId, studentName, submitDate, status

  • status refers to whether they got admitted or not.

  • submitId is auto incremented

Now i wanted to create another table based on that, but only if the status is true, this new table would have the submitId, studentName, submitDate, plus additional fields.

  • this table would have a new auto increment studentId

how would i do that so it automatically updates any new entry to the first table on the second table, but not overwrite the additional content of table 2.

i thought of using a view, but u can't add new columns on the view, right? do i have the logic wrong here or what are the options, could someone please point me in the right direction, thanks

Upvotes: 1

Views: 3082

Answers (3)

Zenilogix
Zenilogix

Reputation: 1393

The first thing you need to do is step back and consider the problem from the perspective of logical entities.

You've identified two entities that I can see - student and submission. "Student" is an obvious entity which you may choose NOT to store in your database, but it may be better that you do. "Submission" is a more obvious one, but what is not so obvious is what a "submission" actually is. Let's assume it is some sort of transaction.

You've mentioned a "second table" without a clear indication of its role in the solution. The best I could infer is that it is meant to be some sort of historical trail on activity against a submission. If true, then I could envision a physical schema sketched out as follows:

Student table. One row per student; contains information about a student (name, id, etc.). Primary key would probably be an auto-incremented number.

Submission table. One row per submission; includes a foreign key to the student table (referencing the primary key); has its own primary key, also an auto-incremented integer. Also has triggers defined for INSERT and UPDATE. INSERT trigger causes INSERT into submission_log table; UPDATE trigger also causes INSERT into submission_log table.

Submission_log table. One row per event against the submission table. Includes all the fields of submission plus its own primary key (submission's primary key is a foreign key here), and includes an indicator field for whether it represents an insert or update on submission.

The purpose of the above is not to supply a solution, or even the framework of a solution, but rather to get you to think in terms of the logical entities you want to model in your solution, and their relationships to each other. When you have a clear picture of the logical model, it will be much easier to determine what tables are required, what their roles are, and how they will be used and how they will relate to each other.

Upvotes: 1

Wirus
Wirus

Reputation: 1190

I think your data model is wrong. Remember that student my have several submissions and there may be number of students with the same name. You must distinguish them. Is there any reason you want to duplicate student data in both tables? If you're new to SQL, read about table normalization first. In you Student table you should store students data and in Submission table - guess what :)

Upvotes: 1

lreeder
lreeder

Reputation: 12206

You want to use a trigger. See:

http://dev.mysql.com/doc/refman/5.6/en/triggers.html

You can create the trigger so that when a row is inserted into submissions with status=true, it inserts a row into your new student table. It would look something like this:

delimiter //
CREATE TRIGGER sub_ins_check AFTER INSERT ON submissions
     FOR EACH ROW
     BEGIN
         IF NEW.status = 1 THEN
           INSERT INTO your_new_table (student_name, submit_date, submit_id) VALUES (NEW.student_name, NEW.submit_date, NEW.submit_id);
         END IF;
     END;//
delimiter ;

Then create another trigger so that when a row is updated in submissions, it updates the row with the same submit_id in your new table, like this:

delimiter //
CREATE TRIGGER sub_ins_check AFTER UPDATE ON submissions
     FOR EACH ROW
     BEGIN
         IF NEW.status = 1 THEN
           UPDATE your_new_table SET student_name = NEW.student_name, submit_date = NEW.submit_date, (etc..)) WHERE submit_id = NEW.submit_id;
         END IF;
     END;//
delimiter ;

Upvotes: 1

Related Questions