Choosing to store data in SQL vs ZODB
A reasonably common question that comes up on the Zope lists is "when should I store data in an SQL database instead of Zope?" This how-to lists some of the things to think about when making this decision.
This document should not take the place of your proper design process, but will hopefully give you a few other things to consider.
feedback, suggestions, to firstname.lastname@example.org
Will the data be frequently changing?
If the data is going to be frequently changing, then it's not a good idea to put it in the ZODB. Every time the data changes, a new record will be appended to the Data.fs file - this could become a problem very quickly.
Phillip Eby noted that this is actually a limitation of the standard Zope FileStorage implementation of a backend data store. Other packages, such as Ty Sarna's BerkeleyStorage do not have this limitation (although they don't provide the Undo and Version support, as a consequence. (but then, neither does an SQL database))
Does the data need to be accessed by other tools?
Do you have a requirement that other tools need to be able to access the data (e.g. various reporting tools). Unless you're willing to rewrite these tools to all work off the ZODB, again, SQL will be the way to go.
How large are the objects?
Largish blobs of data might be better in the ZODB than in an SQL database. Depending on the database, you may find that the size limitations of objects that you can insert will constrain you - for example, my reading of the PostgreSQL docs is that nothing larger than 32k can be inserted (although this is being addressed)
How does your data fit into an SQL database?
Tres Seaver writes:
This brings up a good point - if you are actually storing objects, with subobjects, methods, attributes, and the like, you may find that the work to get the objects into the database in a useful form is high, for not a lot of gain. You might also want to look at some sort of hybrid solution, where some of the attributes or data goes in the DB while other data goes into the ZODB.
Do you need full text indexing of the data?
Most RDBMSs do not provide any way to full text index data that is stored in them - if this is a requirement for you, consider using ZODB objects together with ZCatalog to allow this. (noted by Butch Landingin).
Some more notes on indexing and the like, loosely structured...