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.

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 → jerf]

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 → DanielBMarkham]

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

[bockris → jerf]

One of our custom applications that was started in the early 90's has about 750 tables and it only handles the order management and part of the manufacturing process for one division of our company. That doesn't include back office stuff like GL, AP, AR, Inventory, Payroll, etc.

[henning → jerf]

so can i see any of this stuff or is it locked behind corporate doors just like all the other enterprise projects i hear about?

[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...

[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 → SwellJoe]

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...

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

[SwellJoe → spokey]

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.

[arnabdotorg → SwellJoe]

+1 on Drupal. It also implements sequences, etc.

[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.