I am not sure how to ask this question, but I hope I will be making myself clear.
We are building a CMS-like site for the most part, but the customer also wants the capability of adding a prices table for goods that can be consumed by a user on a daily basis.
So far, there is a custom content type 'user daily report' whose purpose is to store info of the likes of:
user1 - 2015/05/01 - 1 breakfast - 1 lunch - 3 drinks
At the end of the month, I have to be able to provide a report which summarizes how much the user has to be charged for his/her accumulated consumption.
I'm not sure how to exactly phrase my question here, since I'm fairly new to Drupal. Therefor I'll try to describe my requirement / question using some language I'm more familiar with:
Have Drupal create a products table, a prices table (foreign key-ed to the products table), and add a couple fields to the daily report content type (product_id, amount).
In order to create the monthly report, I think I would just use a grouping SQL query (with group by and sum).
The above is not very 'drupalish'. But at the same time, I fail to see how the products and prices tables could be implemented using content types.
To make the question more precise, it is actually a 2 parts question, being:
1) I am not sure that putting the products and prices info inside of Drupal is best achieved by using custom content types. What is the best way to go for you people when faced to this type of functionality?
2) What is the best way of extracting/summarizing the info at the end of the month? I have struggled my way using the views module, but being myself much of an 'old school programmer' I am not necessarily confident that all I need can be achieved by using 'views'.
With all that in mind, my question is: what's the best approach to achieve this goal in a way that integrates well with 'the Drupal way'?
Any hint on the topic is most welcome!
Thanks In Advance!
PS. I've tried the entity-references + views_calc way with a wee prototype. In general, I feel satisfied with the tests I've carried out.
What we need to be able to implement is several tables of products/prices available to different sets of users. All of it based on organic groups (we are using an openatrium distribution).
Using an entity-reference as a field in the users content type, and then adding the necessary relationship when using the views module, I can get what I need. Plus with the Views Calc module I get the summarized stuff.
I still have to try it all in our fully fledged openatrium dev install and see what happens when associating products/prices to groups. But so far, seems promising.
I've learned that my original version of the question didn't fit the questions policy because it was too loose and prone to answers based in personal opinion vs facts (and sorry about that). Maybe the edited version will prevent it from getting closed?
I'll choose Pierre's answer as right, as it has certainly been helpful.