Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I understand that, in order to add functionality to nodes and node types, extra fields must be created in the database.

Now, I don't see why a one-field-one-table schema is used instead of favouring the schema of just a single table for a content type, and then, in that additional table, all the extra fields. What are the adventages of that approach?

I have a site with 1,000+ tables and is being a mess to administer.

share|improve this question
up vote 11 down vote accepted

Because you can reuse the fields and attach them to more than one content type for example.

You actually get 2 tables for each field: one for storing the current version of the data (field_data_field_MACHINE_NAME), and the other one for storing the "historic" (if revisions are enabled) values (field_revision_field_MACHINE_NAME). Some content types can have enabled revisioning, meaning that shared fields need to be "flexible" enough to follow up with that, but at the same time store the values for content types with disabled revisioning.

If you had everything in one big table for content types (I think D5 did this), it would be a really big mess to manage, especially if you would share fields between entities (e.g. nodes and users).

Few suggestions:

  • See this answer by Clive for more information.

  • Inspect this on a clean install just to see how it works. Create 2 content types, add some fields (share some of them) and track what has been changed and how is the data stored in the database. You could probably dig some documentation page, but I think this is the best way to understand the schema.

  • Reuse the fields whenever you can in order to optimize the database more. Be aware that there might be some limitations (e.g. list options, file uploads, required or not, etc.) that will not let you customize fields for every instance, so you will have to create new ones.

I wrote this fairly quickly, but I hope I managed to explain it.

share|improve this answer
    
Thank you very much for such detailed answer. Now I see the reason of that schema. Also I am seeing that for Field Collection, for example, and specifically nested field collections, it is almost mandatory to have this schema instead of a multifield one. – Cesar Feb 25 at 8:45
    
Sure, glad it helped. – Aram Boyajyan Feb 25 at 9:06
    
With CCK in 5 and 6, when a field was on a single content type, it was stored in the table for that type. When it got added to another content type, it got split out into its own table. – MPD Feb 25 at 14:40
    
@MPD gotcha! Thanks for pointing that out. – Aram Boyajyan Feb 25 at 18:10

Great answer from Aram! Which includes an explanation of how "custom fields" are technically implemented in a Drupal database.

If you wonder where the "schema" (as in the question) originates from, I suggest a higher level of abstraction, which I (still) call the "data model" of which the Drupal database is a technical implementation. And that automatically leads to "Database normalization", a concept introduced by E. Codd. The most important concepts I remember from the time I graduated are these:

I do remember there were even more (like 4NF, etc.).

But the "Rule" we always used to measure the quality of our data model was this:

"Everything depends on the key, nothing but the key, so help me ... Codd" ...

About the actual "creation" of such schemas (diagrams), eg to model many-to-many relationships: back in the 90s (the time of Windows 3.1 and OS/2) there was IBM's "AD Cycle". Part of the "hype" then was to use "CASE tools".

One of them was "Bachman", an "expert system" created/inspired by Mr. "Charly Bachman", who started his career at Cullinet, and was heavily involved in the creation of IDMS (an DBMS used in mainframes ... still today!). That's also where the "Bachman diagrams" originate from (which are still in use today ...).

We used the Bachman tool to:

  1. automagically validate our data model (the wait messages was something like "wait while the bow tie expert advisor is verifying your design" ...) and
  2. automagically forward engineer such datamodel to DBMS implementations for IDMS or DB2 and
  3. reverse engineer an existing database implementation (eg hierarchical IMS) to a data model.

It would be fun (interesting) to do some similar reverse engineering today with the Drupal database schema, to see how "Bachman" would evaluate its corresponding datamodel ... and discover possible violations of 1NF, 2NF or 3NF ... (if any).

Note: More interesting background info can be found in Add foreign keys to core, as suggested in @MPD's comment below.

PS: I bet Dries, while he was graduating, must also have heard of Codd ... or "Charly".

share|improve this answer
1  
Very interesting, indeed. I have no formal training in Computers Science, so didn't know about 1st, 2nd and 3rd Normal forms. I had to grasp similar concepts by trial-and-error, reading, experience and frustration. A pity I cannot dedicate more time to learning those basic concepts in the desired order instead of jumping from code example to code example and tutorial. – Cesar Feb 25 at 11:22
    
Well, that's why there are sites like Drupal.SE, no? Too bad it's not allowed to ask for (eg) "tutorials", "how to get started", etc ... – Pierre.Vriens Feb 25 at 11:47
    
I am not sure if Dries has anything to do with the field system. I think yched did the original version of CCK, and still maintains much of the current field system in 7 and 8. See also drupal.org/node/111011 Without true FK support, NF discussions get a bit muddied. – MPD Feb 25 at 14:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.