Good practices working with databases

Question

I'm learning Drupal, and I want to develop a website where the main thing is its database.

I already have the Extended Entity-Relationship Model of the database (kind of complex).

What's the right direction to take when creating a database to be used by Drupal, create it manually and do the queries in Drupal using PHP, as it were a PHP+MySQL site from scratch? Does Drupal provide an easier way to do this?

Edit:
I'm not a Drupal experienced developer. What I want to know is:

• When working with databases, is any better way of doing this that querying the database using SQL syntax, like if I were developing a PHP+MySQL single page?

I just want to know what Drupal developers usually doe when they want to create a database, insert data, modify data, or do some queries and show the data to the users.

Answer 1

Your entity-relationship model is likely going to map to what we call 'Content types' in Drupal.

Content types are defined using the Content Construction Kit module, commonly referred to as CCK.

With CCK, you point-and-click to define what fields you want the content type to have - text fields, numbers, links, references to other content types, references to users in your Drupal site.

With the content types defined, you can figure out what kind of lists for that content you want - tabular, blog-style, etc, and use the Views module to create that listing. Views is a point-and-click query builder, complete with sorting, filtering, access control, dynamic URL arguments, and relationships (think joins) to other content types.

That's typically how 'databases' are done in Drupal - but you don't have to do anything with the database at all, really.

If you've got a really specific use case that can't be solved with some combination of the 7000 or so contributed Drupal modules, then it might be time to grab a copy of 'Pro Drupal Development' and learn module development to integrate your custom module into the rest of Drupal to meet your exact need.

Answer 2

My first reaction is that this is possibly a too-basic question, but on reflection it is worth answering because it is so fundamental to the mindset of working with Drupal that you tend to use the excellent work of others available via modules for most things, and only have to write a little PHP/SQL.

So, unless you're building modules for Drupal you generally don't need to know anything about the underlying database or the way it works.

With Drupal you'll be working Content Types, Nodes, and the modules Views and the CCK to build most of what you need from a site. These modules are building blocks rather than complete drop in plugins that perform a single as one may have seen in less well designed systems. Don't make the mistake of thinking that Drupal is a CMS, it's more like a standard framework without too much coding.

You can get from 0 to 95% of functionality very quickly for most things just using the core and some well chosen modules.

Answer 3

DISCLAIMER : I am not a Drupal Developer

Whenever you use MySQL, you must plan for the amount of storage you expect your site to use.

From personal experience as a MySQL DBA, I have cleaned up Drupal installations and Optmized Queries with a few basic principles in mind.

The main thing I do for any Drupal customer is to CONVERT ALL MySQL DATA TO INNODB. This will eliminate table locking, especially in a high traffic site. You should be all means use the innodb_file_per_table option.

You should make sure you can cache all of your InnoDB data. Therefore, have enough memory to accommodate that. Don't forget to tune the sort_buffer_size, join_buffer_size, read_buffer_size, read_rnd_buffer_size and multiple the sum of them by the max_connections. Make sure the total memory consumed by MySQL (innodb_buffer_pool_size + key_buffer_size + (sort_buffer_size + join_buffer_size + read_buffer_size + read_rnd_buffer_size) X max_connections) does not exceed 90% of the installed RAM.

It is always good practice to review the DB Layer. Once you implement these suggestions, DB Layer maintenance should be consider when you have significant growth in data or connections. Otherwise, it should be a rare occurrence.

Answer 4

If you where a general user, all you would do is install the site via install.php, and the only thing you need to know about the database is the name, user and password, Drupal will take care of the rest.

If you where needing to do some form of queries as a user you would use the Views module, again not really needing to know anything about the database.

If you where a developer writing custom code or a contrib module and you needed to store information in the database, you may need to write some queries, and others can be take care of by some Drupal functions.

• Setting up your database in the first place you would use hook_schema(), where you simply need to return your schema in the required format.

• A best practice approach to writing/updating records in a single table is to take advantage of drupal_write_record().

There's plenty more reading to be found at: http://api.drupal.org/api/drupal/includes--database--database.inc/group/database/7

Also note that documentation is specific to version.

Hope this somewhat answers your question.