设计表mysql

I have project to do – eStudent diary for students and teachers. I think I have problem with my Mysql tables to create them. My assumptions are:

  • all pages require login
  • in system we have 2 kind of users ( teacher and student)
  • teacher can teach a lot of classes
  • every student has his own class ( f.e. 2a, 4b etc)
  • teacher teaches only one school subject
  • every student is assigned to the set of sucjects he learn
  • student can has a lot of degrees from one subject

I must to design tables in mysql, but I think I stuck… I have now this:

USERS:

-id
-name
-surname
-status ( student, teacher )
-email
-password

USERS_CLASS:

-id
-class_id
-user_id

USERS_SUBJECTS

-id
-subjects_ids
-user_id

CLASSES:

-id
-number

SUBJECTS:

-id
-name

DEGREES:

-id
-user_id
-subject_id
-degreees

I wonder or is this is correct way to design it? Or should I change something?

Seems good. Actually, you can add created_at and updated_at columns with timestamp() to almost all your tables. For column status in users table and degrees in DEGREES table set Enum type in mysql, because it may have only few string values. And of course, foreign keys.