I'm very new to Drupal, but have been a database developer in both PHP as well as other languages, and am an analyst and data modeller at the corporate level. I love seeing smart ways of achieving flexible, highly dynamic content. I've been delving into Drupal 7 to see what I've been missing while developing my own solutions. As I've been using Drupal to create content types and content, I've been keeping an eye on the database.
Keep in mind this is not totally a criticism. The UI is awesome. I'm trying to determine why certain design decisions were made with respect to the data model. As I look at the data model all I can see is a schema that stores the data seemingly inefficiently, relying on a large number of queries to retrieve data, followed by heavy CPU and array processing to combine this data, instead of relying on the power of the database to do the join processing and criteria logic/enforcement.
I notice that each field that is added to a content type is it's own table. I would have assumed that the content types would be a control table, with nodes typed accordingly and some sort of flexible data storage (long multi-field records or name/value pairs). Then if I knew tables were being created dynamically for new elements I may have thought that content type data tables were being created with the added fields existing on each record, so a query by content type could return all content type data in one query.
What I am seeing is that queries seem to query the nodes, then query the field tables, then depend on PHP to populate the resulting array and display the result, which seems overly inefficient.
I was also intending on performing some database manipulations to pull certain node sets out of a dev environment and populate them in production instead of doing an entire database backup and restore, but dynamic table creation based on fields (and shared fields between content types no less - am I seeing that correctly?) makes this an exponentially more challenging task.
I want to see the elegance in this model.. I know contributors from all over the world are involved in this project.. can someone explain why this methodology is sound? And why we can't use well designed core tables for smart data storage without dynamically creating new tables for each new element? Is this a historical method from the early versions? Especially considering that performance and sheer query load is the biggest issue Drupal faces.
Comments
Personally I agree 100%. The
Personally I agree 100%. The new Field API (which creates the tables you are speaking of) was originally called CCK in Drupal 5 and 6. The UI was great, as people could create content types with whatever fields they wanted without having to code them. It was used so much by people that they decided to integrate it with core.
However, I personally never used it for the exact reasons that you state - it creates bloat and inefficiency. Unfortunately it's been forced on us in Drupal 7, which I personally don't think was a good move. Part of Drupal's beauty has been the fact that it was very streamlined upon install - but this feature seems to go against that concept.
Maybe you'd like to assist in re-writing that part of the code to put all the fields for a particular content type into one table! That would be the ideal solution. I'd sure like to see it.
Contact me to contract me for D7 -> D10/11 migrations.
Drupal 6 had this!
So after reading the above post and scouring the web for other opinions/solutions to the above architectural decisions/design/eccentricities I found mention regarding CCK in Drupal 6. I didn't realize that the above post actually meant CCK 6 did exactly this, creating one data table per content type and adding/removing fields to this table. I've now been working with Drupal 6 as it's far easier to do any direct data manipulation this way. The only complaint I have is the constraint to have completely unique field names, and not field names unique by combination of content type and field name. I mean really - if I want to add first name to two different content types they may be really different fields - although this limitation is just on the fieldname, and not shared data, which is good.
Why would Drupal 7 see individual field tables as being a step in the right direction? Plus - I found someone saying it was a big bonus that you could share field tables between content types - woo hoo. This solution would never pass any solution review I've ever been a part of. Complex content types may have 50 or more fields, so pulling back one complete content type record would involve hitting 50 different tables (either multiple queries, or one query joining 50 tables, lol!!), which is really an incomprehensible way of doing things. Crazy.
I'm going to ramp up and develop on 6, as 7 has some questionable design decisions.
I couldn't believe it when I heard it..
And I actually ignored it.. Never going in to test it myself.
Yes.. this new 'feature' is ridiculous, imho.
However, your understanding of the Drupal 6 / CCK methodology is not complete. They actually implemented part of this insanity in CCK 6.x:
The new fields, if completely new, were added to the content_type table. NP, there. I loved it. Get everything from one table, one query. The problem arises when you try to reuse a field in a new Content Type. At that point, CCK takes that field, creates a new table for it... I believe it's content_field_something copies the original data to it.. then drops the column from its original table. Thereby doing essentially the same thing that D7 does with fields. But only if it's used 2 or more times.
This also lends to why each field name has to be unique if you don't want to reuse them. How do you then create a unique table name for a reused field if another content type has one by that name already? Can't be done.
I've imagined that D7 was released with this flaw to cut down on the amount of logic and checks that need to be done when reusing a field.
I, like anyone else who understands the downfall here, hope to see this change back to the D6 CCK methods. Learning D7 now.. but not willing to use it on any sizable site. Just TOO many headaches to anticipate.
Translations and Revisions per a field
I believe this field storage method was used because of Translations and Revisions per a field.
For a more detailed review of this have a look at these links.
Content Translation (field translation) in Drupal 7: First steps
Drupal 7's new multilingual systems (part 4) - Node translation
With the new fields system I believe you can also have different storage system where your fields are stored in a different backed-end.
Field API tutorial see Field Storage
Mongodb see mongodb_field_storage
And I believe its still possible to have node based store like in Drupal 6.
"values for all limited-value fields in a bundle in a single table row" Per-Bundle Storage
I do not yet fully understand Drupal 7, so I cant give you details but, I hope this answers some of your questions and anybody else that finds this post.
Thank You!
Logged in to bump this, as i'm still not sold.Just curious... If one were to make/contribute a module with their own tables using hook_schema, will this be discouraged in d7?
I'd say no. D7 has the schema
What are you trying to do? Maybe easier to give you some feedback on a specific idea than a broad concept.
Contact me to contract me for D7 -> D10/11 migrations.
Thanks for the reply
Something as simple as a new, semi-complex content type. In this case i'm transitioning a d6 module (using schema, that had a parent and child table) to d7 (using fields API).
Interesting thread, I kind of
Interesting thread, I kind of like the way it's now in D7, it makes it easier for me to remember where the field is in the database based on the field name, also it gives more flexibility like being able to assign to different entities and helpful to handle the translation stuff like NaX pointed out. For the cases where you need keep everything in a single table for performance probably is better to write a custom content type or entity within a module but you'll loose the field API to use with them.
http://ericmaster.ninja
Ericmaster
how to change appearance of fields of a content type
how to change appearance of fields of a content type. I am new to drupal. if someone can help please tell me that i am using drupal 7 and my default theme is bartik i developed a form using content type fields. I have 10 fields in my form that collect some information. i just want to change alignment of fields and labels. i dont know where to add or edit. i do not know how to access its html file and where it is.
I agree with the above. As a
I agree with the above comments. As a fairly new convert to Drupal having been schooled in the fine art of creating fully normalised data structures, I could grit my teeth and kind of forgive the semi-ludicrous way that CCK in 6 goes about things. When trying to upgrade my modules to drupal 7 and seeing the horror of the database schema I think I'm going to just have to find another CMS to develop in, as it is just utterly insane how they've gone about it. Not everyone wants to use a point and click UI for delving into the database and creating bloated auto generated queries - if they did then dreamweaver users would be in high demand.
Not everyone wants to use a
And not everyone has to. I used D6 for years, almost never using CCK. I've built sites in D7 that didn't use fields. Fields and Fields UI are modules, and do not need to be enabled if you wish to do things differently.
Contact me to contract me for D7 -> D10/11 migrations.
But it then limits the point
But it then limits the point in using drupal in the first place if you can't leverage all the functionality from creating your content as nodes. Drupal 6 at least gives you the option of having a non-mental database structure, even if you do have to have unique fields (a small price to pay).
The more I look into drupal 7 the more I realise that I would find doing an upgrade impossible. All queries to any many to many intermediary tables would require a ludicrously convoluted join query in place of a very simple select. I hope that they changed the way this works in Drupal 8 and I will skip a version, as 7 is just bonkers.
_
not at all and, imo, if anything, d7 is way ahead of d6 in this respect. It's the entire point of entities really. You can create your own entities and get lots of node-type functionality for free (ie views, references, etc). There are probably still modules that could use some entity awareness, but just being able to use views and entityreference (or relation) is a huge time saver.
I fully agree. Having to
I fully agree. Having to force nodes to be everything/anything in D6 was a major limitation for me. I created a module once that tied in with an IMAP email system. I wanted to create it to be dynamic, so it could handle multiple mail accounts, and as such I had to force a node to be a record holding connection details for each account. This was very unnodelike behavior, and it was a real headache. In D7, I created a entity to handle this, and it was much, much smoother.
Contact me to contract me for D7 -> D10/11 migrations.
I'm afraid I don't understand
I'm afraid I don't understand the problem you faced here. Why didn't you just use a custom table? Whats the problem with it being a node? Its just a record in a table after all. I wouldn't have used a node for something that held account settings personally, but I don't see the issue, at least you'd have a more conventional and efficient database than splitting it across however many tables for each field. Unless I'm misunderstanding you?
I just don't see how having all the fields in separate tables is efficient or sensible. What about that point raised earlier with regard to the 50 odd join statements to get a single record? It seems grossly inefficient.
Perhaps I'm putting too much importance into using nodes in order to keep hold of my data, but I can't deny the use of having a centralised node table that allows me to standardise a lot of custom functions, and of course all of the node functions that are at your disposal.
We'll you answered the
We'll you answered the question from your first paragraph with your last paragraph. I wanted the functionality that comes from tying in with the node system. I had a few modules in particular that I needed that worked with nodes.
As for why fields are done this way, it's because it allows a field to be attached to any entity, making the system more versatile. On my current project I'm attaching a particular entity type to a few different entity types, and this allows me to use those images in views, independent of the entity types themselves. It's database normalization (google it). As for the 50 odd joins, we'll that's why you use caching mechanisms. The first time it's generated takes time, but then it's cached and it doesn't matter how many joins there were.
Contact me to contract me for D7 -> D10/11 migrations.
Ok, so you wanted the node
Ok, so you wanted the node functions - what exactly is the problem you faced then with having to use a node? As I said, it's a normal record in a table after all.
Wow. I clearly know about database normalisation pal, having been a database designer for the last 5 years, but as you should know there needs to be a compromise between efficiency and normalisation. The guy who started this thread clearly knows about database normalisation too, you want to condescend to him too? This design is normalised yes, but it's also wholly reliant on the core functionality to get the data back.
Tell me how exactly you cache a process. Like, for example, processing expiries for users medical licenses and the effect that has on it's dependant competencies, then the effect that has on a users eligibility for work. How exactly does that get cache'd? That query because ENORMOUS.
I notice also, that you actually agree with the original poster and have since seemingly changed your mind. What caused this?
Field Storage is modular/pluggable
@mykmallett
My understanding is that Field Storage is modular/pluggable meaning that you can write you own field storage module. The default core module is Field SQL storage, but there is also mongodb field storage. So if you want things stored in a different way using a different schema then you can write your own storage backend.
Mongo db is nosql, which
Mongo db is nosql, which isn't really feasible for any of our solutions, despite the performance increases it offers. I think we just will have to consider using a different platform
Thankyou for the information tho
Well, I didn't need nodes,
Well, I didn't need nodes, because nodes are essentially an article, with an author, revisions, timestamps etc. I didn't need any of that functionality for my email account. The author was irrelevant, I didn't need to track revisions, and I didn't need timestamps. When using nodes, they were showing up in the content page, but they weren't really content that needed to be shown on that page. And it wasn't the node functions I wanted, I wanted to be able to tie into other modules. But in order to use modules that work with nodes, I needed to use the node system.
I'm not sure what you mean by caching a process. We are talking about entities, a data type. If you give me more information I can probably explain more clearly, but your example above seems to make some assumptions on common knowledge we would share, but I don't know what dependent competencies are, how they affect users' eligibility and what relevance that has to an entity. But basically, the caching with entities works generating an entity then caching it as a BLOB with an expiration timestamp. So when the entity is needed, if it hasn't expired, the BLOB is used, and if it has expired (or doesn't exist) it is generated, with all the joins from the fields attached.
This thread was started in the first month after D7 was released. I had spent the entire month working with it, but was still thinking with a D6 mindset. Fast forward to near two years later, two years spent mostly working with D7, and I've seen the light (as it were).
Contact me to contract me for D7 -> D10/11 migrations.
It's sounds like you largely
Do you largely work from the front end, out of interest?
The details of my process aren't important. The important thing is that fields from one data type are being used to calculate important things in others. This becomes far more convoluted when using this methodology for the database. If a process has to run nightly - ie getting fields from 3 different data types that are related through a foreign key relationship to use to calculate something in another type that is also related - then it cannot be cached and it becomes very inefficient. I understand how cacheing works, thankyou. My point is that you can't cache a process. You can only cache stuff that is for display. Not all data types have to be used for display, in fact, very few. The great thing about drupal, seemingly, was that it was useful for use as a proper system, not just as a blog with different post types.
Lets say I have 200 people as one data type.
Each of those has 10 qualifications with expiry dates, these are also data types.
These are joined with a one to many table of 'qualification for person'. Standard stuff.
Nightly, a function must run to see if any qualifications have expired. Now this is an incredibly simplified version of our system, but the point is that this is a check on (with this small number), 2000 content types. Now, with the new idea of having each field in its own table, to get the fields that are required (about 10 altogether) that requires querying and joining 10 tables each time. And that's before any calculations take place. And this is a small percentage of the overral functionality
Do you see what I'm saying here?
@mykmallettI understand what
@mykmallett
I understand what you talking about with regards to joins. You can change this with a custom SQL storage module if you have the time. Another way of implementing this is to use a view executed in programmatically. This way the view does all the heavy SQL lifting. I think an API for querying entities would be needed to make this easier, the same way Active Record works in other frameworks. You could argue that node_load/entity_load method would be easiest but as you have pointed out this is very inefficient.
Also: Done anybody know why the Per-Bundle Storage module was abandoned.
http://drupal.org/project/pbs
Turns out nobody was
Turns out nobody was interested in PBS, because nobody needed it.
It gives you small savings while leaving the biggest actual pitfalls intact.
You can optimize entity querying with Search API, and entity loading with Entitycache.
Your main problem is usually querying, and PBS won't save you there because you still have joins (base table + one table per bundle + a table for each unlimited field. Add a condition on the bundle table, and a sort on the base table, and MySQL has once decided to go index-free, requiring additional optimization).
I'd never do a single site without Search API (using Solr) again, it's that good. If you're small enough not to need it, then the joins aren't a problem for you either.
The whole concern in this thread is a non-issue, which is why you'll find very few posts about it elsewhere.
If you're doing custom calculations, you want a custom entity type with your own properties in your own base table (of course, having a few fields won't hurt).
CCK in D6 wasn't an answer to that, and neither is Field API in D7.
bojanz has started to point
bojanz has started to point in the direction of just why the D7 Field API is so non-normalized.
What we are looking at now is a stage late in the evolution of the system. Yes, once upon a time the very first attempts at user-defined data schemas (flexinode etc) could try to just overload the node table with more columns, and there were less joins.
This option is the one that "makes sense" to non-Drupal coders who have written some PHP by hand before now and think they know their way around making their own database schema. It's not wrong if you are building one thing once..
Nowadays however, if you choose to use the Drupal framework and build re-usable tools,
Fully thinking through the schemas and the pitfalls of supporting just these three simple-sounding requirements should help you see why the direct approach of just putting all field values in one table or less joins away can't really work generically all the time. And working all the time, for everyone, even the point-and-clickers, is what this approach supports.
If the problem *you* are solving today doesn't actually seem to need all those features right now, that's quite likely. You may also know that you'll never need to do translations, and that a number of your values aren't going to need to be multiple (according to todays business rules anyway). Next year you'll probably wish you'd supported revisions, but that's next year.
But other users of the same system will have different requirements, and you can't prevent them from *wanting* and having multiple fields in places you didn't think they were needed.
You can still do it your way if you prefer
So you have the choice,
That is still allowed.
The first option was pretty common design pattern in earlier versions of Drupal (eg location module, or biblio), but all of those have had to retro-fit support for those big three data axes later or have been overtaken entirely by rewritten modules that did embrace the field API.
image.module was a an optimized single-schema content type from that generation, and certainly one of the top ten modules ever in its time. But it was totally discarded and replaced by imagefield. And gained some JOIN weight because of that. But also revision support and multi-attach ability.
The field API does give you a little extra conceptual overhead, but that overhead is taken care of out of sight most of the time (until you try to debug SQL joins by hand). It does also give you access to a thousand other modules and utilities and plugins and renderers that work within that structure.
If you think you're doing it wrong, you are
As ever with Drupal, if you have convinced yourself that you have to do more work than seems necessary, you probably are right about that. If it seems that joining ten tables by hand seems convoluted - you are right. The solution to this problem however is not "this is stupid", the answer is "that's what the API is there for, use that!"
.dan. is the New Zealand Drupal Developer working on Government Web Standards
Thankyou for your very
Thankyou for your very informative answer, this is what I was looking for,. As you say, there is little chance I will need translations and such, but revisions is one of the reasons I used nodes in drupal 6. My background is actually database design, I'm from the school of designing a solid database for a solution that allows you to leverage the true power of the database, rather than process afterwards using PHP. I will have to look into this, and see where things go. Not that I'm bad mouthing drupal as I love what is available for it, but I've seen solutions come and go, and I want to gauge if this actually has staying power before we can commit to such a huge change.
CMS at scale.
I'm now looking at this issue. For the design constraints listed above, the current solution seems reasonable. Thanks for the explanation.
However, for any solution at scale (millions or hundreds of millions of documents, 10 x thousands users, several score fields) this implementation will fall over.
If you find yourself looking at this possibility. I'll suggest an approach. First three questions:
Premature optimization applies here, and most people are not gonna hit this. Throw bigger machines at it - they're cheaper than developers any day. And the fugly DB schema? Deal with it.
If the answer to all three of these is still YES:
The question of whether you want to pre-budget #3 will depend on your circumstances. In my case when I really looked at my needs I decided the scale issue is probably optimistic. I'll run with the Fields implementation as long as I can.
Perhaps an interesting module would be a "fields migration" that does all this automagically.
My 0.02.
We're building a site right
We're building a site right now that needs to be scalable in the future, as the site will have millions of entities (for the rest of this post, I will call them nodes, though we aren't actually using nodes, we are using a custom entity). To handle this, we have added a Redis database as a layer above the MySQL database, to allow for faster Node retrieval.
To do this, when an Node is saved, the Node object is serialized, and saved to Redis with a unique key to allow for retrieval.
For example, if I have create Node 456, then I would create a Redis key named Node-456, with the value for that key being the serialized node object. Then the load function for the Node (called with entity_load) loads the Node from Redis rather than MySQL. The value is then unserialized, giving the entire Node object with its fields.
Redis can handle retrieval much faster than MySQL, even with millions of rows, so this handles the scalability issue. Of course, it adds a lot of complexity in that it requires installing Redis, and overriding various existing CRUD functionality to handle working with Redis along with MySQL.
Contact me to contract me for D7 -> D10/11 migrations.
_
Thanks to bojanz and dman for FINALLY, really explaining this architectural decision!
When d7 was released I was very concerned when I first learned of this because I had d6 content types with many many fields and spent a LOT of time researching it without being able to find any coherent reasoning. This, in turn, forced me to completely rethink a d7 'upgrade' for my main app, which turns out was a good thing, as I reorganized things to use more content types with less fields per type using entityreferences to tie everything together. This ended up being a much better and more flexible architecture so my concern faded.
Still it was nice to finally see the reasoning behind it. Well done-- and thanks!
Do you largely work from the
No, on the contrary I work almost exclusively backend. I use very few contributed modules, and use Drupal more as a framework, rather than a CMS.
No, but that doesn't mean that there aren't other ways to run your process in an efficient manner. But I can't really say much more than that, as you've clarified that the details of your process are not important.
Honestly, only somewhat. But I'm also not sure if you are looking for a solution, or looking to vent. If you are looking for a solution, I'm happy to work with you and others to figure out a way to solve your problem in an efficient manner (though another thread focused on that issue specifically would probably be more appropriate), whereas if you are looking to vent, then my understanding is mostly moot.
Contact me to contract me for D7 -> D10/11 migrations.
Inefficient schema
Today i've connected for first time to a D7 database, and i've been near to have a heart failure...
I remember in Drupal6 we have the possibility to have global fields, but normal fields where inside a table. This means that if i'have a content type with 40 fields we need
a) One query with 40 left joins to obtain the values
b) 40 inserts instead 1 when i insert a content
Break a normalised database structure is a technique used for improve performance (like create calculated fields, or duplicated info for faster reports).
This structure is not efficient. IN D6 i made some test with big tables (of several thousands of items), and the performance of only 1 table select used 10% of the time of a query with 10 left joins of global fields...
Other things that doesn't considered this structure is
1. Database Administration.... 200 tables of fields in a project'??? We're crazy???
2. Integration with other applications.... I've made an automatic Excel that connect with ODBC, and the effort has been very big.... And i don't say if we need to integrate with Business Intelligence Tools....
3. Perfomance in edit and save items .... There is not any cache systems that can says that we don't do 40 queries with 40 fields. Also, this increase the complexity of Data Access methods.
Well, there're other issues that make me said that the actual implementation is a big error, and only has be though for be used by Drupal Websites developers that doesn't need knowledge in SQL. I can assurance that if you show this architecture in the some Computer Science Faculty , design won't have more than 2 over 10 points.
I'm a very strong defensor of Drupal, and i know some issues difficult to solve. I applauded when CCK becomes part of the core, but i didn't know some big issues.
In this case is a back step.
Do you know if D8 will have the actual CCK creation table structure?
_
nope.
Personally, I don't understand this decision either. The only info I could find was at:
http://groups.drupal.org/node/18302
http://drupal.org/node/361042 ("Field Storage" section)
http://groups.drupal.org/node/17564
http://groups.drupal.org/node/9297
http://groups.drupal.org/node/11487
One of the posts mentions a vote and more evaluation to be done, but I never did find the thread or issue where the decision was resolved and explained. I'd love to see it-- especially since http://drupal.org/project/mv and http://drupal.org/project/pbs (the supposed mitigation strategies) seem abandoned.
I guess this thread is a
I guess this thread is a great example of Nietzsche's infinite return, all of the arguments and counter-arguments repeat every 5 months.
_
sort of, lol. But there still has to be an issue somewhere that captures the actual decision and though I found quite a few discussions, I never did find the actual issue.