Come together with the global Drupal community in Rotterdam, 28 Sept – 1 Oct 2026. Sessions, contribution, connection, and Early Bird savings until 8 June.
By orev-1 on
Does Drupal database design suck?
See:
http://www.hiveminds.co.uk/the-drupal-database-design-is-a-comedy-of-errors
Comments
He thinks so.
Some people disagree with him.
- Robert Douglass
-----
My Drupal book: Building Online Communities with Drupal, phpBB and WordPress
wasted talent
If I had the knowledge 'to think' Drupals database was messed up, I'd sure help fiz it. I'm sure it's not perfect, 'by the book', but what software is?
There are way too many good things about Drupal to trash the one or two 'proported' overlooked issues, afterall this is a 'co-op'.
Druapl is Awsome!
He Uses Drupal
Oddly enough, that user uses Drupal to run his site. If it sucks so much, I am not sure why he continues to use the software. *shrug*
He also has a questionable history on the Drupal forums.
What do you mean
What do you mean Brian@brianpuccio.net "He also has a questionable history on the Drupal forums"...
He has hurt the ego of the Drupal developers ? Is that questionable?
No ego involved involved.
No ego involved involved. He's rather tragic actually. He has a history of posting rants, demands and flame fests and refusing to contribute to the project. It's rather sad. So he posts all these articles about what mean people we are all over the Internet but still continues to use Drupal. I beleive he's also working on a fork.
It's all really sad.
-Steven Peck
---------
Test site, always start with a test site.
Drupal Best Practices Guide -|- Black Mountain
-Steven Peck
---------
Test site, always start with a test site.
Drupal Best Practices Guide
forks?
Are there really forks off drupal? I'm new to it (first donwloaded 2 weeks ago and I'm launching my site with it in about 2 hours). I'm just suprised to hear that. I didn't know drupal had been around that long.
Just curious.
so much information,
so often ignored. :)
handbook
background
history
UID1 for this site
No forks have survived. The one 'actual' fork has been refocused back into Drupal core to the benefit of the entire community.
Drupal's success has been build upon a steady evolution of code, knowledge from lesson's learned and a growing base of solid contributors. It's not a 'sudden success', merely more people are starting to pay attention and reap the benefits fo the project.
-Steven Peck
---------
Test site, always start with a test site.
Drupal Best Practices Guide -|- Black Mountain
-Steven Peck
---------
Test site, always start with a test site.
Drupal Best Practices Guide
wow.. talk about hot air
If he would point to some specific "errors" I might be interested...
(and the relational qualities that are lacking might be true on a very zealus level, but that's a mysql related design decision, not an error)
Not convinced, but interested
As someone in "the transitional phase" to Drupal, I find and article like this to be very interesting. However, I also find the total lack of specific examples of the authors claims, lack of examples of how things should be done, and lack of explainations of what advantages these changes translates into, result is this article having almost zero credibility. If you are going to write such an obviously contriversial article such as that, you have to back it up with proof.
----------------
Dominic Ryan
www.it-hq.org
Proof yes, Patches even better.
Indeed!
Even better - you can back it up with patches to fix things.
Adrian Simmons
adrinux@perlucida.com
Adrian Simmons
adrinux@anaath.at
Backing things up with proof
Backing things up with proof seems to be a concept that is foreign to this author.
Anyway, as a long time contributor, I can state frankly that indeed database design is not the top quality of most Drupal coders including myself. We could really use some people with experience for tweaking the occasional query or even optimising the design of tables.
Carl seems to miss some kind of referential integrity within Drupal's database scheme. That's true, there is none. This is due to the fact that we still support MySQL 3. I personally would gladly drop MySQL support for better referential integrity as soon as possible. Unfortunately, this would mean a jump to MySQL 4.1 and I am not sure how feasible this is, ie how many hosters still use MySQL 3 or 4.0.
--
Drupal services
My Drupal services
--
Drupal services
My Drupal services
Interesting
The project I work on in my day-job is a bit like that. Large-scale enterprise level system for the grocery industry. The decision was made to support 3-4 different DB systems via an abstraction layer, and to keep the referential integrity checking in code rather than in db constraints. Sometimes, a step like that is an informed decision rather than a mistake.
--
Jeff Eaton | I heart Drupal.
--
Eaton — Partner at Autogram
Well, if we come to the
Well, if we come to the conclusion that keeping integrity in the code is better or easier to do, then we should of course keep it this way. But we should have the discussion first.
--
Drupal services
My Drupal services
--
Drupal services
My Drupal services
inspired me to contribute
Hey I really like this post. It is helping me understand the problem better that I've been seeing / having.
A lot of what I'm developing for drupal wouldn't be useful for other drupal users. In fact, it would only be useful to my competitors, so I haven't really been able to come up with projects that would help other drupal users.
As I've been tieing things into the database, I've often thought, "why is it done this way?" but this makes sense to me more so, thanks to your post.
I'm going to begin helping develop what I can to help with the database. I've seen some practices in phpbb (I was trying to alter this to my likeings, then quit and started using drupal instead) that I think could help with drupal. At least they could help make drupal more customizable.
Anyway, thanks for the understanding.
Constructive?
Thank you for your opinion orev, now how about some constructive criticism. Please explain to us what your statement is based upon. This is open-source. If you do not like it, improve it (that's unless you paid someone to 'create Drupal' for you and you are merely directing your frustration to the wrong place). Or even better, if you are not willing to pitch in to improve Drupal, join Joomla, Xoops, Documentum or Vignette to work on something you may like.
Please explain your outburst
-----
iDonny - Web CMS Development, Design, and Web Marketing Advice
The author of the article is
The author of the article is updating his article with an example.
I have some experience with
I have some experience with database normalisation and It's so that when one delves into a database, it can get very very complex. The problem with projects such a drupal and any other project for that matter is that while the project is developing some information structures can change a bit and some adjustments are needed.
I didn't look at all the article, but I've seen that the guy talks about the information redundancy concertning "Forum"... he might have a point there... but thats small point in IMO ... Most database are FULL of errors and if you want to go looking for faults and / or errors in an always developming project it means that you miss the point of ... erm ... everything....actually...
In comparison to other OS packages drupal is vey efficiently programmed.
It reminds me of a site (wich I can't remember what the name was ) ... This site totally degraded and made PHP look like the wordt invention ever..... There were NO good points written whatsoever.... The pathetic things was that the site itself was actually written in .... guess what ... PHP ...
Gdev
Database SIG
Suggest setting up a database group at http://groups.drupal.org/
Drupal platform is continuously evolving and new approach and design improvements are facilated by open source model.
----
Darly
I am not impressed....
...not impressed at all by this article. In fact I'm generally thoroughly unimpressed by anybody who takes an attitude of "I know it all so much better than you" - even when they do (and in any case superior knowledge is never an excuse for insulting people), and in this case it is not clear that he does.
The article starts off by slagging off Drupal's database design as "really stupid": now I have seen many, many IT mistakes in my life but it is rare that they can be slagged off as "really stupid" - usually, even when they turn out later to have been mistakes, they had some logical reasoning behind them. In addition - even if the Drupal core team are not DB experts - given the quality of the core code I find it hard to believe that the DB design would be "really stupid".
Then when he gets round to giving an example, the only example he can find is truly pitiful. Forced to admit that "to be honest I have only found two queries that just are not needed", our "Mr Superior" illustrates, in detail, a case which (inasmuch as I understand Drupal and I'm open to correction) will occur just ONCE in the life of a Drupal site - when you set up your first forum.
So I don't think that I will turn to "Mr Superior" for advice or even any useful thinking about the curiosities that have puzzled me in Drupal and have caused some problems. But this thread might be a good place to start thinking about them?
1) Lack of foreign key constraints and transaction support. I see (in this thread, not thanks to "Mr Superior") that the lack of foreign key support is due to the need to support MySQL 3.0. Is this also the case with the lack of transaction support? The problem with having to code foreign key constraints is:
a) It's more complicated (code it right once in the database and it will always be done right, even by contributed modules)
b) If your code to cascade updates breaks in the middle of a transaction to delete or update, how do you make sure that the DB is left in a coherent state?
c) Lack of transaction support means that people can walk on each other's toes when doing updates (an issue I've tried to address in the "checkout" module). In effect, this means that Drupal is not really suited to really large sites, or even to moderate size sites where the content updating is widely distributed.
2) My one serious performance problem with Drupal queries came when my hosting service complained that my pages were causing MySQL to eat up memory. I came to the conclusion that this was because there was an enormous amount being done at the back end, notably by calls to nodeapi and to the vocabulary, terms, nodes, and comments tables. This seemed inevitable given the fact that I had blocks displaying taxonomy terms with the number of nodes per term displayed on every page. I didn't go into detail investigating the exact reasons for all these calls being generated - in the end I simplified the front page blocks and replaced the default nodes display by a custom module with a custom SQL query that runs like the proverbial s**t off a shovel.
3) Why does the Search module use its own indexes as opposed to Fulltext indexing? I'm using fulltext with trip_search at the moment, and it seems to work ok
To conclude, one thing that Drupal obviously got RIGHT was the iron rule: all identifiers are numeric, non-modifiable, non-visible, and non-reusable. That is key to the whole system and it would have been nice if Mr Superior had bothered to acknowledge the fact.
3) Why does the Search
Would this be because the InnoDB storage engine does not support fulltext indexing (yet)? As of 4.7, Drupal will be storage engine agnostic, so if a user installs their Drupal database on a server which uses InnoDB by default and Drupal used MyISAM fulltext indexing for the search engine, wouldn't this break it?
----------------
Dominic Ryan
www.it-hq.org
Part of the reason that not
Part of the reason that not all database backends we support allow for fulltext indices (think mysql 3). A more important reason is that we think that a carefully built index with weighted keywords is simply superior.
--
Drupal services
My Drupal services
--
Drupal services
My Drupal services
Normal Form
I'm no expert, but I have a book over PHP and MySQL, which talks about 1st, 2nd, and 3rd Normal Form, if you've ever heard of that. I'll pull out the old book and explain it, for educational purposes only. Please correct me if I'm wrong.
Normal Form
First Normal Form (1NF) states that you can't have fields that hold multiple values in one column (such as address, city, state, zip). In a way, I see the "data" field in the users table as breaking this rule. I don't know what all of 'a:1:{s:5:"roles";a:1:{i:0;s:1:"2";}}' means, but if someone were to go in and try to change it manually, there would be a high risk of breaking it. Also, the variables table has some cryptic value fields as well.
Second Normal Form (2NF) states that it must be in 1NF, and columns from multiple tables with a repeating value must be turned into their own table. I think drupal is doing good with this one.
Third Normal Form (3NF) states that it must be in 1NF and 2NF, and basically that each table has to have, and be dependant upon a primary key (at least I think). I think drupal is doing well with this one too.
My Opinion
So basically, all I know of that's wrong is some problems with 1NF. Drupal has some internal processing that would require some hacking to understand, which I'd rather just be able to look at a database and be able to get an idea of what's happening on the website.
It seems to me like taxonomy is doing a good job. I'm no export, but the data seems to be well organized, and does it's job using joins.
And another thing about his example under _forum_get_vid, one is a type, and another is a module. A module does not have to use the module name for a node type, so his reasoning does not seem to make sense.
There's always need for improvement, and criticism can be good a lot of times. So I applaud those that know what they are talking about, and try to improve drupal, and OSS in general.
--
Bradlis7.com | Churchofchristnet
serialize functions
What you're talking about there with 1NF is the PHP serialize functions and that's an intelligent choice on the part of the developers. There are certain fields that contain multiple values that can't be determined pre-runtime, installation, etc, or would make the table structure have too many fields. In addition the data in those fields isn't handled by the database anyway. You can actually change those fields by hand if you understand the syntax that the serialize function produces. So I think that's a moot point, in this case I think its a preference over elegance and functionality compared to rules that may make sense to novices, but some higher wisdom has been used to correctly subvert those rules. I like your last statement, but if there's one thing that geeks won't tollerate are people who claim to know, but know not. If you can't contribute, then don't complain and beggars can't be choosers... What I mean by that, just to be clear is that Drupal is a developer community, so if you say that this is wrong, then show us with code and examples how to make it right, i.e. a patch. If you choose to use Drupal, and don't know how to write Drupal, then don't complain about its flaws, because you really have not paid for what you're using, if you had, perhaps it may be better -- and that's a great reason to get your suggestions implemented by funding a module or even a patch.
--
showcase | blog | rob's book
I actually figured that out
I actually figured that out after writing that comment, because I wrote a small module myself. It's given me insight on how to make a non drupal site that I work on a little bit more dynamic.
The more I look at drupal and the underlying code, the more respect I gain for drupal as a whole. The original developers had a lot of vision, and could see ahead to be able to make something this stable and extensible.
So, a big thanks to Dries, and all of the other main developers out there maintaining this amazing piece of software!
--
Bradlis7.com | Churchofchristnet