help with database relationships scenario entity relationship diagram.
Posted on 2012-03-25
We are building a system that will have student, parent, and teachers using the system.
We want to the system to beable to show relationships between the users, so on a student profile it would list their teachers, and parents, Parents would see their students (child) and their teachers. Teachers would see their students, and the students parents details. It's a long time since i did entity relationships so very rusty.
I have tables called
Users (user_id, firstname, lastname, address1,address2,city,zip,phone,email,password, created_at,updated_at)
teacher_users (id, user_id, student_id, parent_id, created_at, updated_at)
student_users(id, user_id, teacher_id, parent_id, created_at, updated_at)
parent_users (id, user_id, teacher_id, student_id, created_at, updated_at)
Do those table look correct ? and how would i define relationships, many-many etc.
teacher_users,student_users,parent_users have One user Users has many Teachers, Students,Parents.
Teacher would see many students, 1 or 2 parents for that student.
student would see many teachers, and 1 or 2 parents.
parent would see many teachers and 1 or many students (might have a few kids)
How would i show these in a ERD ?