Relating two tables in a database

Question

I have a "Student" table which should have name column. But I made another table "StudentsName" for name since names have three parts: firstname, middlename and last name. Now my question is how should I relate these two tables?

Answer 1

Yes, you need a primary key/foreign key constraint between the tables. The only question is "which table is the controlling one?" It could be the student table, in which case the name refers to the student ID in the student table. Or, it could be the name table, where the student table records the current name for a student (and there might be other tables to record the history of this student's name changes - marriage, or just legal changes of name).

Answer 2

If "student" table uses ids from "name" table then you want to have a FK from "students" to "name"