Relationship problem with Student activities, comment by the teacher and dependents

[tanha]

Hi,

I would like to track students activities by the teacher and this is visible by the students parents "dependents" and therefore the parents could give comments to the activity issued by the teacher and the teacher could respond to parents comment too.

So I designed the ER Diagram, but somehow it does not seem correct to me, could someone please give me feedback in this regard.

NOTE:
For more information see the attachment

[vee_liang]

In my opnion, there's no right or wrong in drawing an ER diagram. It will all be based on your understanding toward the case and requirements that you are facing. So why don't you start by listing your requirements in points. From there you can analyze what are the objects of your database. Just list the objects down as it is describes in your requirements. e.g:

Objects identified:
- Student
- Teacher
- Dependent
- Activity
- Command

From it you move to the next step by identifying what relationships that each objects have. e.g:

Relationships identified:
- A teacher teach students
- Students are being taught by teacher
- and so on...

now based on those two lists that you have now, make a level 0 diagram. Which draws the objects and its relationship as it is. Don't get confused with the many-to-many relationship which make you have to create a new object, yet. From there you can, then, draw the level 1....up until to the level n.

Now with that I hope you can be even more sure with your diagram. I'm sorry if it doesn't help much but, again, in my opinion designing/modeling a database is an art. Everybody may have different approach in developing to most appropriate design. Yet, one thing that each database designer must have is a really good understanding toward the case and its requirements. Have a nice day.

[tanha]

Thanks for the information and the reply,

Actually I don't care about the rest of the ERD such relationships among teachers, students, classes, subjects and so on, BUT the most important point was what mentioned on the post.

Thanks again :-)

[tesuji]

Hi tanha

It is always a good idea to start database design with an entity-relationship model. Depending on whom you design the ERM for, there are more or less flaws present. First, relationship student many-to-many dependent allows that a student would have many mothers and fathers.

This impossibility can be solved by two many-to-one relationships between student and dependent. Because primary key of dependent now appears as foreign key (fk) twice in student, what is not allowed, this fk must be given role-names, e.g. fatherID and motherID.

Second, in your comment-table there are comments from dependents and teachers. Assuming the primary keys are those given in your ERM there would be intransitive dependencies (dependent -->> comments, teacher -->> comments), thus 3NF not fulfilled. Therfore I would decompose comment table into two: teacherscomment and dependentscomment.

Third, from the viewpoint of relational theory (Codd, 1NF-3NF etc) and what students usually learn in database course at college/university surrogate/artifical primary keys those replacing the keys which define the many-to-many relationships are not correct.

Such surrogate keys should be replaced by natural primary keys as for example in table activity: This table originates from student many-to-many teacher. Therefore primary key of activity must be (studentID, teacherID). If such a pair has more than one content value associate, a third attribute, e.g. date/time the content was generated (your create_at column) should be added to pk.

One may interpose using compound keys would wast storage and time what might be simply avoided by one-column surrogate keys. This conception usually does not consider that surrogate primary keys almost always require additional secondary indexes = keys to calculate inner joins efficiently.

-- tesu

[tanha]

Thank you very much for the instructive information.