Drupal Best Practice for Complex Relationships
I am developing a Drupal 7 site 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:
- Performance: When rendering pages, queries should be fast and easy.
- Relationship: need to have a two-way relationship (=> can list related content based on content or vice versa)
- Views Integration: Should be helpful and easy to list related content in Views
==> I come up with 2 solutions:
Method 1: The Drupal Way I know there are some modules like EntityReference, Field Collection, ... but dont know how to use them, mix them right. Say how to create a content type for a class, its fields that reference a custom table, and then easily show a list of students in a given class?
===> Question: What is the best practice for my case and what modules should I use and mix together to solve this problem . What type of content, its field and relationships should I create?
Method 2: may not be the Drupal way
Usually I will create some tables in 3rd normal form ( 3NF ) for these objects and their relationships: student, teacher, class, exam ... I mean this approach might not be the Drupal way as usual with magic field_xxx tables for each custom content type field, right? Below are examples 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,.....
===> Question: If I do this, is there any module that supports auto-generated CRUD forms, or an API to create a form to manipulate these tables, it's easy to allow field with the Views module.
Please correct me for any misunderstanding and your ideas are welcome.
thank
source to share
Drupal with a link to Entity Reference, Views, Field Collection (I would add Inline Entity Form , Views Bulk Operations , Views Megarow ), in my opinion, good: you can create back-office (and front) screens very (I have in mind very) quickly using Views, related content (using Entity Reference) can be selected in both directions, Inline Entity Form allows you to create creation forms containing a form to create related object (so create the object and its associated object at the same time). Drupal Commerce is a good example of what you can do with these modules (see entity relationship model ). Rulescan help customize your business logic. So points 1 and 2 (maybe) are, in my humble opinion, satisfied with the Drupal way.
BUT...
This flexibility comes at the cost of complex queries and ultimately poor performance. You will find in all the posts about how Drupal is slow and when you start playing around with a complex setup it can be a major problem. But with good server configuration (cache, proxy ...) and no PHP in Views :), this can be done (I run several Drupal Commerce sites, with a good server and good sysadmin this is fine).
And you have to dive into Drupal logic, Views logic, rule logic, which can be frustrating at some points: something that you would code in multiple lines of PHP would make you have a lot of clicks in the Drupal interface. Of course, for complex things, you can always create a small custom module that will only do what you need.
About the second solution, I have no experience for sharing Drupal and a custom data model, but I think you should consider application frameworks (Symfony, Zend, CakePHP ...) if you want to be free for DB design.
Good luck.
source to share