WingNews logo WingNews GitHub [2]
top | item 940570

Ask HN: Open Source apps with lots of database tables?

11 points| henning | 16 years ago | reply

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?

14 comments

order
[+] jerf|16 years ago|reply
"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|16 years ago|reply
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.

[+] bockris|16 years ago|reply
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|16 years ago|reply
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?
[+] cmelbye|16 years ago|reply
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|16 years ago|reply
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|16 years ago|reply
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)

[+] arnabdotorg|16 years ago|reply
+1 on Drupal. It also implements sequences, etc.
[+] jakecarpenter|16 years ago|reply
Here at my day job we have a at least 5 dbs/apps with more than 500, and a few with north of 1000.