I'm new to database design. Hope someone may able to help me.
I'm try to design a database where students can take class, and teachers teach classes. The tables are: users, enroll ( to connect users table and class table), class, course, and roles (to determine the privilege of the user whether is a student or teacher).
Because of the security reason, I would like to seperate the Roles from within the Users table, but then I run into trouble when designing the "roles" table.
1st question: How can I link these two tables together?
2nd question: Is it better to seperate students into Students table and teachers into Teachers table, or just put all of them into Users table to simplify the design?
tip: create some sample rows for each and every table, using values other than 1,2,3, e.g number your students 13,14,15, your teachers 56,57,58, your classes 101,102,102, your courses 330,331,332, and so on
you should be able to see how the tables are related by looking at the sample rows, where the primary and foreign key values should actually link the sample rows in a consistent manner
when you get to the roles table, you will have a much better feel for what columns it requires