Adding a table with items and prices to a drupal site [closed]

Question

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.

Answer 1

Your question consists of 2 questions ... I'll use the same numbers to answer them.

1. Alternatives to using Custom Content types

a) Use Drupal Entities (Option 1)

Use the Entity Construction Kit (ECK) module to create 'entities' (= fieldable things ...) like Products and Prices. For each of those entities use "fields" for each of the columns you mentioned. Should you run into fields such as dates, you would have to add modules such as date also.

For the "foreign keys" use the Entity reference module (possibly some related modules of it also, extending its functionality, as mentioned on the ECK project page) and/or the References module (cfr. 'node_reference' and 'user_reference' as contained in CCK for D6).

Maybe modules like Display Suite or Panels could help to enhance the look and feel of it all also?

b) Use custom tables and/or an external database (Option 2)

Custom tables are tables you create yourself, in the same Drupal database. External Databases are databases that are different from the Drupal database, which are typically in formats such as Oracle, PostGress, MS Sql, MySQL, SQLite, some PDO compliant DBMS.

Using this alternative, you can use the Forena module for reporting about the data from within a Drupal site (as further explaining within "reporting" below also). To perform updates (delets, inserts, etc), you'd use your own tools that you'd prefer for the DBMS of your choice. Same for creating the tables, etc.

2. Reporting

Consider using modules such as (either one, or both, depending on your requirements):

• Views, combined with Views Calc also probably. Have a look at How to calculate the sum for each option of all webform results in a Drupal webform grid? to get an idea of using Views Calc.

• Maybe you want to consider the Forena module as a supplement / alternative. E.g. if you'd need to also create charts or so, or if you have a need to access data in some external database such as MS SQL, Oracle, etc. Here is a quote about it:

  ... built of the idea of using SQL to get data out of a database and use XHTML and CSS to format it into web reports.

Disclosure: I'm a (co-)maintainer of some of the modules mentioned,
I hope this does not violate the site's policy on self-promotion.