Database design

I am not sure but I think that having indexes on boolean fields and short repeated text is not very efficient in a database. The reason I am unsure is because my training and experience is in Microsoft databases where you just don't do this. I have no idea how MySQL handles this but it looks wrong. What I am talking about is the Drupal CMS node table and the ten or so table indexes that it has. There are four columns in this table that are just ones and zeroes, boolean values or intergers 1-4 or repeated node type names. How can adding indexes to these help anything? Indexing 2,000 table rows by two columns where the first column's value is 90% "blog" and second column's value is 1 or 0 has got to be inefficient or at the very least unneeded.

I also noticed that after installing 20 or so contributed modules that Drupals database becomes over-normalized. This is mostly because Drupal promotes the use of table JOIN's. While this may not effect a small site it may be cause for database or table corruption in a busy website with hundreds of users. I am not sure how to fix this but I think expanding the core tables by some of the more commomly called information rather than having to add new table would help. Another thing would be to actually add in some JOIN tables rather than doing on the fly JOINS.

This article brought to you by the Hiveminds Magazine - Staff. Contact us if you want to post an article or announcement anonymously