I have to create a database and I want to know what would be the best solution.
Let's say I have to store information about students and teachers.
Should I make one single table containing all the personal information (name,email,phone password) to both students and teachers?
For additional information should I keep them in separate tables as ADD_TEACHERS and ADD_STUDENTS?
You may want to create a people table for the common data like name,email etc. You can then use a primary key column in this table, and if there has to be information specific to teacher (like course_instructor, head_teacher of class), then use that unique key from people as reference in your course_information table. Do the same for students too.
It sounds like your best route is to have a separate table for teachers and students. You'll have what is called a one-to-many relationship between teachers and students: one teacher, many students.
But that only covers one semester. When you consider other semesters, you have a many-to-many relationship between students and teachers.
In the end, you're best off with at least three tables: Teachers, Students, and teacher-to-student relationship.
First thing you have to do is Map the entity relationships (ER) and its attributes.then design the database and apply the normalization strategies.
In the proposed scenario students and teachers are different entities . and find out thier relationship
Then check out their attributes Example - Student(Name,Class,DOB)
It depends on how you are going to use the data. If you have a lot of use cases where you are interested in "persons", regardless of role, or if you have a lot of persons who are both teachers and students, you may want to learn the gen-spec design pattern, as it applies to relational tables.
