Drupal best practice for complex relationships

Question

I'm designing a Drupal 7 website where nodes/entites have complex relationships (1-many, many-to-many). For example:

Student (registered user) can belong to one ore more classes
Student can take one or more exams each semester
Teacher will take a note about each student in his class after each semester

My main concern is about:

1. Performance: queries should be fast and simple when rendering pages
2. Relationship: need to have two way relationship ( => can list related content based on content or vise versa)
3. Views Integration: should be fieldable and easy to list related content in Views

==> I come up with 2 solutions:

Method 1: the Drupal way I know that there are some modules like EntityReference, Field Collection,... but don't know how to use, mix them the right way. Let's say how to create content type for a class, it's fields that link to user table and then easy to show the list of students on a given class?

===> The question is: What is the best practice for my case and what modules I should use and mixing together to solve this problem. What content type, it's field, and relationship I should create?

Method 2: could be not the Drupal way

Normally, I will design some tables in 3rd normal form (3NF) for those entities and their relationship: student, teacher, class, exam...I mean that this approach could be not the Drupal way as normal with field_xxx magical tables for each field of an custom content type, right? Hereunder are example of them:

Student table ( uid, name, full_name, other meta data columns) , of course the uid is foreign key point to Drupal user table
Class table (id, name, code_name,...)
Student_Class junction table (student_id, class_id, semester_id)
etc,.....

===> The question is: If I do it this way, is there any module which support to auto generated create CRUD forms, or API to create form to manipulate those tables, easy to allow field-able with Views module.

Please correct me for any misunderstanding and your ideas are welcome.

Thanks

Answer 1

The Drupal way with Entity Reference, Views, Field Collection (I would add Inline Entity Form, Views Bulk Operations, Views Megarow) is, in my opinion, the good one : you will be able to create back-office (and front end) screens very (I mean very) quickly using Views, related content (using Entity Reference) can be fetched both way, Inline Entity Form allows you to set up creation forms containing the related entity creation form (so create an entity and its related entity at the same time). Drupal Commerce is a good example of what you can do with these modules (see this entity relationship model). Rules can help to set up your business logic. So points 1 & 2 are (can be), in my humble opinion, satisfied with the Drupal way.

BUT...

This flexibility comes at the cost of complex requests, and eventually bad performance. You'll find all over the web posts about how Drupal is slow, and when you start playing around with a complex setup, it can be a serious problem. But with a good server configuration (cache, proxy...) and no PHP in Views :), it can be done (I run several Drupal Commerce sites, with a good server and a good sysadmin it's ok).

And you'll have to dive into Drupal logic, Views logic, Rules logic, which can be frustrating at some points : something you would code in a few PHP lines will take you a lot of clicks in Drupal interface. Of course, for tricky things, you can always build a small custom module doing just what you need.

About the second solution, I have no experience to share on Drupal and custom data model, but I guess you should consider applications frameworks (Symfony, Zend, CakePHP...) if you want to be free for the DB design.

Good luck