I am trying to figure out how to build my web application in Drupal 7.

We have around 300 Content Types. A number of key tables have > 100 columns.
The database is fully normalized with the exception of some OLAP tables that have periodically summarized data, optimized for reporting purposes.

Some tables have hundreds of thousands of rows. Altogether we have several thousands distinct fields in the database (no redundantly stored data except foreign keys and the OLAP tables).

With exception of some lookup tables, many tables are very dynamic and at least some data changes every few hours and sometimes every few minutes.

In the current version of the app we have our own modules with our own SQL statements (we have several hundred such performance optimized queries).

Given the new content-by-field structure of D7 (as opposed to content-by-table), I am not sure how we would make our application work in D7. Some specific questions:

  1. What is the recommended approach for dealing with large, complex data structures in D7?
  2. What can one expect with regard to performance for large data-driven web-based OLTP applications in D7?
  3. What is the rationale behind the switch to this new data structure? I have heard that it is performance related due to the existence of "shared data". What is that? I have never heard of "shared data" in a well designed database.
  4. Is there any documentation that describes the rationale behind the new architecture or the thought process leading up to it?

I hope someone will respond as I do not want to reject D7 out of hand and my experience so far is that there are a lot of very smart people involved in the architectural design of Drupal. I am sure they had something in mind when contemplating this switch and perhaps if I understood that logic I could come up with a solution for my application.

Thanks in advance for any insights.

Comments

joe.murray’s picture

joe.murray commented

I have similar needs. As best as I can see at the moment, you want to be using the schema and schema api modules to define your tables. Each table will be an entity (see the entity and entity api modules). Support for foreign key relations did not make it into D7 core. I believe that either Relation or er (Entity-Relationship) D7 modules will eventually provide support for foreign keys.

If you set up an additional database connection to an external DB (for convenience - you could also just shove all of your tables in Drupal's DB), then you can get the schema module to read the database structure and produce the php code that defines the schema in the format required by the schema API.

While Schema will produce code for the foreign keys in your tables, at present it is not yet used, except perhaps by CTools.

So, D7 is not yet ready for your app.

A quick response to another point: Sometimes a traditional RDBMS will have many fields in one or more tables that have similar properties. Drupal calls this abstract notion a field, and each time it is used in one or more entities, it calls them a field instance. This allows it to store the shared properties (eg INT, allowable values are 1, 2, 3) in one place. For each field instance, the UI widget that supports it can be specified differently (eg as a select box with labels of Red, Green and Blue associated with values of 1, 2 and 3.

bg1’s picture

bg1 commented

Thanks for the response. I find your comments regarding an external database very interesting. My concern with what Drupal is doing with the "field" is a concern for me because my ultimate goal is to be able to store the content-type data in remote databases, but retain all the value of the node concept (and user interfaces) in the Drupal side. This should be totally transparent and the developer user should be able to use CCK (or like) to define their content types and the module I envision would create the content-type table in a remote database and the node+ in the Drupal database. Editing a node would retrieve the data from the remote database and then update both the node locally and the data in the remote DB as applicable.

Are the entities created as you describe above then nodes in Drupal or simply stand alone tables that you can write your own functionality for? What within Drupal core and the main contributor modules recognize these entity tables?

When you say support for foreign key support did not make it into D7, what are you referring to? Are you referring to formally declaring relationships to enforce/utilize relational integrity functionality? I tend not to formally declare relationships because it tends to slow down the database and I prefer to write my own SQL statements.

Interesting the logic behind the "field". I have to think about it. But I don't see why they did not create an entity for field type with the widget links and then link the table columns that were defined for the content-types to it.

Thanks again, I am still relatively new to Drupal (although I have been working so long with DBMSs that I dream in SQL) and am looking forward to learning more about the projects you mentioned.

joe.murray’s picture

joe.murray commented

Are the entities created as you describe above then nodes in Drupal or
simply stand alone tables that you can write your own functionality for?

They are standalone tables and by implementing a module including entitycontroller and other interfaces we will provide CRUD operations for them. As they will be available as full-fledged Drupal entities, they will be able to be related to notes via a Relation once that becomes available in D7.

What within Drupal core and the main contributor modules recognize
these entity tables?

As entities are full-fledged D7 core objects, I think everything.

When you say support for foreign key support did not make it into D7,
what are you referring to?

It seems I was mistaken - it is in core, but one often has to write code to support them. I'm still unclear after talking to a few key core people today at DrupalCon why I saw that comment on an issue on Dec 31, 2010.

Are you referring to formally declaring
relationships to enforce/utilize relational integrity functionality? I
tend not to formally declare relationships because it tends to slow down
the database and I prefer to write my own SQL statements.

Yes, the formal relational integrity functionality is what I mean. I tend to like to leverage the capabilities of technologies I am using. MySQL unfortunately is very poor at optimizing query performance compared to other RDBMSes I have used. I am quite comfortable writing my own SQL statements, but maintaining integrity and (potentially) improving query plan optimization is something I'm happy to enable by declaring foreign keys.

Best wishes.

bg1’s picture

bg1 commented

Thanks. I tried to research Entity and Entity API in Drupal and I found descriptions of what it does, but I did not find any kind of conceptual white paper description explaining of what is meant by Entity, what its purpose is, why and when one would use entity, how entity fits into the overall Drupal architecture, etc.

As you might have guessed I am not only a relative Drupal Newbie but also new PHP, Linux and MySQL (I am an application architect/MSSQL developer). Can you point me to any introductory documentation that might provide such insights?

Is there a document that provides a starting point for all the database-specific tools for Drupal?

(I guess I am one of those nerds who can't use a toaster until I know what the purpose of the toaster is, why it was designed like it was and how it works - at a detailed technical level. That is probably one reason why I don't eat a lot of toast ;-).

karens’s picture

karens commented
Status: Active » Closed (won't fix)

The only thing in the D7 version of CCK is Content Migrate, a module to migrate D6 CCK data to D7 fields. It is not a place for a high level discussion about how to architect Drupal sites in D7, let along how completely unrelated modules, like Entity, work.

This discussion does not belong in the CCK issue queue. It is not an 'issue' that can be 'fixed' by the CCK maintainers.

RumpledElf’s picture

RumpledElf commented

I think I have subscribed to most of bg1's threads on this topic :)

I have the same interest, on a site I've already started in D7 but nowhere near as complex as bg1's. It has about 10 content types only, each with around 10-20 fields. I'm using D7 effectively as a user management and data input framework, and for that it works absolutely perfectly BUT the database schema it creates is beyond awful, and being for authorised users only it needed a lot of tweaking to stop it running slow without caching. My D7 site needs to expose its data to another application that iterates through a good chunk of the database on a regular basis to generate some other data for a second application, and I started writing this query:

"select t1.field_title_value as title, t2.field_textorimage_value as textorimage, t3.field_format_value as format, t4.field_destination_nid as destination from drupal_field_data_field_title as t1, drupal_field_data_field_textorimage as t2, drupal_field_data_field_format as t3, drupal_field_data_field_destination as t4 where t1.entity_id = $nid AND t2.entity_id = $nid AND t3.entity_id = $nid AND t4.entity_id = $nid", only got to 4 of 16 columns and was just itching to write "select * from contenttype where nid=$nid ...

Of course I'm taking some serious abuse in irc for wanting a normalised schema and for wanting to write queries in a non-drupal application (these other apps are in C++) and generally being made to feel like I'm the only person in the universe who has a need for this utterly useless normalised schema thing, despite having found quite a bit of heated discussion on the field-per-table thing while googling. At the moment I'm working on the assumption that noone who really wants to do (from scratch or a conversion) a high-performance website/webapp in drupal is using d7 yet. Its a bit of a catch 22, the kind of sites that need this aren't on d7 yet, the backend (pressflow, authcache, pbs etc) isn't there yet, and you need both to go ahead. Its all there in d6 so again I'm assuming it'll all happen for d7 in due time.

The very first thing I designed for this webapp was the database schema to get it as efficient as possible and it is quite frustrating not being able to implement that in drupal. Because my app exports data and doesn't do much with it within Drupal itself, in the interim - while wavering between continuing using D7 in the hope pbs happens soon or stopping using Drupal altogether - I've compromised and write a normalised copy of the data out on hook node update and insert.