Parent Child Concept in SQL Server Relational Database

Question

I have a Teacher and a Student table in SQL Server. I want to use the inheritance concept to make a table with the name Person and put the common attributes of Student and Teacher there.

Practically how can I do it?

What I want to know is:

• how a Person record will show in Student table?
• If I update the name from Student how will it get updated in Person table and etc.

Please help me I don't know anything about to try and I need it badly please...

Answer 1

Here you can learn about creating relationship between tables.. https://www.simple-talk.com/sql/t-sql-programming/questions-about-primary-and-foreign-keys-you-were-too-shy-to-ask/

Answer 2

"Teacher" and "student" would be your tables that inherit from "Person".

person stores all the details that are common for both teachers and students (eg, name, address, contact info etc)

teacher stores specific info for teachers, and a foreign key pointing to the person table to identify their personal information. similarly, student stores information specific to students

your person table could look like

+-----------+---------+----------+------------+
| person_id |  fname  |  lname   |    dob     |
+-----------+---------+----------+------------+
|         1 | tracey  | wright   | 10/10/1990 |
|         2 | max     | smith    | 11/11/1998 |
|         3 | chris   | brown    | 12/06/1978 |
+-----------+---------+----------+------------+

lets assume that tracey and max are students, and max is a teacher.

Your teacher table could look like

+------------+-----------+------------+
| teacher_id | person_id | speciality |
+------------+-----------+------------+
|          1 |         3 | maths      |
+------------+-----------+------------+

teacher_id is the table identifier, and person_id is the the one that chris has in the person table. This now identifies chris as a teacher

Similarly, your student table could look like

+------------+-----------+------------+--+
| student_id | person_id | detentions |  |
+------------+-----------+------------+--+
|          1 |         1 |          4 |  |
|          2 |         2 |          5 |  |
+------------+-----------+------------+--+

This now identifies tracey and max as students (and for example, specific information regarding the students might be number of detentions)

In each case, with teacher and student, person_id is used as a foreign key. Foreign keys are primary keys within another table, thus allows other tables to reference each other.

In order to access the information for each record, you would need to use a join statement. Depending in the DBMS you are using, this varies in syntax (see http://www.w3schools.com/sql/sql_join.asp to get a proper understanding)