mysql创建表另一个表,mysql基于另一个表中的列创建表并自动更新

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

解决方案

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.