Solved

help with database relationships scenario  entity relationship diagram.

Posted on 2012-03-25
1
647 Views
Last Modified: 2012-03-26
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 ?
0
Comment
Question by:jonathan1978
1 Comment
 
LVL 2

Accepted Solution

by:
Sandeepratan earned 500 total points
ID: 37764809
this can be achieved by using relates tables, u can refer below design

1. People ( id,name,address,other fileds)
2. RelationType (rel_id,name)  (example 1,'teacher',  2,'parent'
3. PeopleRelats (id1,id2, relationid)
 
this is basic layout, you can extend the same for specific relations
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question