
Ask HN: Open Source apps with lots of database tables? - henning
I hear a lot about developers working on big enterprise applications with 150 database tables. None of the projects I've worked on have required more than 20-30 and their relationships are usually pretty simple.<p>Are there any open source applications out there with big data models? I've seen MediaWiki's database schema (http://www.mediawiki.org/wiki/File:Mediawiki-database-schema.png), are there larger ones out there that would be worthwhile to study?
======
jerf
"big enterprise applications with 150 database tables"

Did you drop a zero or two? I hit 250-ish once on a relatively minor project
by enterprise standards. (Built a small business on this table, so it wasn't a
dinky thing either.) If you're normalizing anything like correctly, the table
count grows fast. (Not to mention things like what I call "enumeration tables"
that exist solely to provide foreign key checking on another table and make it
so you don't have the same string showing up a bajillion times, instead just
having a tinyint or smaller.)

~~~
DanielBMarkham
Depends on how the data model got created.

If there was sufficient refactoring, you should be able to run most
enterprises on a small-ish number of tables. Say 100-200 (not including
lookups) I'd almost bet that you could go <100, but I'll hedge my bets a bit.

If you don't refactor, and most enterprises don't, then yes, you can run
easily into the 1500-3000+ range. You can also get this big if you buy a one-
size-fits-all data model, which has to be God's punishment to mankind for
being able to draw boxes and lines. You can also grow fast in a hurry if you
have a team who love "patterns" and are not afraid to plop them in everywhere.

Most enterprise problem domains do not require anywhere near the number of
tables they use to do their business.

~~~
towndrunk
Yes. I can attest to that... my enterprise day job product has 1395 tables and
2286 views. It's a real mess actually.

------
sunkencity
If you want to look at enterprise style LAMP you can look at Magentos
database. <shudder>. It's 250? tables of pure dba geekery:

[http://www.magentocommerce.com/wiki/_detail/doc/magento---
sa...](http://www.magentocommerce.com/wiki/_detail/doc/magento---
sample_database_diagram.png?id=development%3Amagento_database_diagram&cache=cache)

------
JoeH
Take a look at Compiere ERP (<http://www.compiere.com>), they have an open
source community edition. It has been many years (6+?) since I last looked at
it, but it easily had over 500 tables.

In fact a quick search shows 531 Tables and 108 Views:
<http://www.knowledgesuccess.com/local/doc/schemaCompiere253b>

------
sprsquish
OpenMRS is pretty big. I don't know the exact number of tables, but it's up
there. <http://openmrs.org/wiki/Data_Model>

------
cmelbye
I was about to suggest MediaWiki before I even opened the link ;-)

Going in depth in their database schema and code might be a good idea, it's
got a lot of really cool optimizations (which makes sense, considering it
powers a top 10 site on the internet).

------
SwellJoe
Drupal and Joomla both have about 150 tables, and most modules add a few
tables, so a working installation with reasonable functionality usually hits
about 200 tables.

~~~
spokey
Wait, really? You guys must be using many more modules than I am. I just
checked a couple of nearby development boxes with various Drupal projects on
them. The smallest had 41 tables. The largest had 92 tables. Most had fewer
than 50. (These are primarily Drupal 5 deployments, maybe Drupal 6/7 add a lot
of tables?)

This seems to be a list of the tables in a default Drupal 5 installation:
<http://webdevgeeks.com/schemaspy/index.html>. It looks like Drupal 5 has
around 40 tables out of the box.

Here's an ERD for CivCRM, which I believe is built on top of Drupal, so this
would be additive to the base Drupal install and whatever modules CivCRM
depends upon:
[http://wiki.civicrm.org/confluence/display/CRMDOC/CiviCRM+ER...](http://wiki.civicrm.org/confluence/display/CRMDOC/CiviCRM+ERD+2.0)

Here's an ERD for Joomla: <http://www.oozman.com/oozing/ideas/archives/erd-
joomla/> (v 1.0 and 1.5)

~~~
SwellJoe
My current Drupal install for Virtualmin.com has 184 tables. I don't think of
it as a particularly big Drupal installation, but I don't have a lot to go on.
It has forums, shopping cart (Ubercart), issue tracker and project modules,
and a bunch of the usual suspects for analytics, search, etc. Two of the
tables are for our custom modules for our license manager and shop
integration, and a few more are legacy Joomla->Drupal migration data tables
(which I planned to use to make old URLs work, but the URL stuff in Joomla was
so insane that I gave up on it, and just leave them around for manual lookups
just in case). We migrated from Drupal 5 to Drupal 6 before launching the
site, and I'm pretty sure we had about 160 tables when it was Drupal 5, with
roughly the same functionality as the Drupal 6 version, but none of the
migration tables.

I guess maybe the Ubercart stuff is responsible for a lot of the disparity...I
see a lot of "uc_" tables in my Drupal database.

------
jakecarpenter
Here at my day job we have a at least 5 dbs/apps with more than 500, and a few
with north of 1000.

