Drupal as (powerful) relational database

Question

I’d like to make a Drupalsite, providing a platform for people around the world who are working on the same research topic to collaborate and communicate. (I’m sure Drupal is therefore an excellent choice).

However, this website should also include a relational database (which will be a very interesting research tool for the visitors of the website). A test version of this database already exists in ooo base: it has 50 tables with a lot of many-to-many and one-to-many relations between them. Very basically it records the relations between persons, events and organisations.

I know it’s possible to create a relational database in drupal by creating custom contenttypes and by using the entity connect module. However, having a look at the way Drupal stores these data in its mysql database, I’m afraid this way of building a database isn’t very useful for me, as it seems very difficult to query the data (or to use [social network] analysis software on them). The possibility of querying the data easily, is crucial for my database.

I know it’s possible to add a second database to the drupal site (cf http://drupal.org/node/18429). However, I’m not sure whether that’s the solution for my problem.

Very short I’d like to know:

(1) Whether it’s possible to change the way Drupal saves the data of some content-types in its mysql database, to a way which seems more “natural” for analyzing data (and being able to query them easily)

(2) Whether it’s possible to add a second mysqldatabase to the drupal site, which organizes the data of some content-types on a custom (more ‘natural’) way. If possible, the users of the drupal site have to be able to add content and to analyze these data, as if they were saved in the same database.

I’d be very thankful if someone could help me!

Answer 1

You have a good start on working with the databases. From my own experience I am loath to rely/allow Drupal to maintain any sensible data. And since you already have a robust database it sounds like it would not be a good option for you either.

With all that said, it seems like you want to simply hook into the node/content saving that occurs in drupal. You will then be able to connect to an external database and process and insert the data anyway you choose.

Node API Hooks - D6 has similar functions, just named differently. There are a lot of hook points in the process. You can even alter forms and add your own submit functions.

Answer 2

If you want have your own workflow to save content types and its fields in database you may take look at Field Storage API. It is used to perform all "database access".

For example, you can take look at MongoDB module to see how it can be implemented. (I see that you need SQL DB just use this module as example).

Also take look at article "Remote entities in Drupal 7" that describes how the whole entity (i.e content type) can be saved in other place than default Drupal DB.

Answer 3

I recently started a sandbox for Drupal <=> Neo4J integration.

http://drupal.org/sandbox/Letharion/1861580

It's still in very early stages, but Neo4j sounds like it would be a way for you to allow much better querying. The idea is however not to change the way Drupal stores it's data, but rather duplicate the links between content.