tag:blogger.com,1999:blog-3346090548501966296.post3116910441972543309..comments2024-03-26T05:03:04.875-07:00Comments on Perspectives on LedgerSMB: The Case for Intelligent DatabasesChris Travershttp://www.blogger.com/profile/06211762965865744803noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-3346090548501966296.post-70254015030593905552012-01-02T06:22:17.980-08:002012-01-02T06:22:17.980-08:00Hi Tony:
your points deserve some more discussion...Hi Tony:<br /><br />your points deserve some more discussion than I can give in a comment. I will probably post a response. For some applications, you may be right, but when one gets into complex line of business applications where data must be re-used, I think a lot of that breaks down. More soon.Chris Travershttps://www.blogger.com/profile/06211762965865744803noreply@blogger.comtag:blogger.com,1999:blog-3346090548501966296.post-58206461817986335402012-01-02T02:44:13.133-08:002012-01-02T02:44:13.133-08:00I think this article is so wrong on so many levels...I think this article is so wrong on so many levels. I tried to post my comments here, but your blog software rejected them for having too many characters. Instead I have posted it on my own website at <a href="http://www.tonymarston.co.uk/php-mysql/the-case-against-intelligent-databases.html" rel="nofollow">http://www.tonymarston.co.uk/php-mysql/the-case-against-intelligent-databases.html</a>Tony Marstonhttps://www.blogger.com/profile/05261538105715665206noreply@blogger.comtag:blogger.com,1999:blog-3346090548501966296.post-6365318140969590502011-11-13T15:57:21.149-08:002011-11-13T15:57:21.149-08:00Thanks for your comment.
This is one thing I thi...Thanks for your comment. <br /><br />This is one thing I think is worth emphasizing:<br />"We decided to design an ORM around the database design and not around an OO design, that's why we don't use Hibernate or something like it." <br /><br />One thing I'd ask is whether one might still be better off with some level of encapsulation. For example using VIEWs to mediate betweeen the ORM and a clean relational design.<br /><br />"- portability across RDBMSs. In our case portability has been a winning factor in one big project where we were forced to use MS SQL Server (...) instead of PostgreSQL"<br /><br />That's a significant tradeoff with a procedural interface. Procedural interfaces are not particularly portable, though I suppose porting between PostgreSQL and Oracle might not be so bad. Certainly going to MS SQL is out of the question.<br /><br />"- SQL is not a "db schema refactoring" friendly language, and in general is not a developer friendly language. Just think about the case of changing a primary key in a table (we don't use the "every PK is surrogate" pattern). All the joins in all SQL involving the table must be changed just for starters. Our framework today allows a change like this to happen nearly transparently, with full compiler checks. Normally SQL can't be checked statically. You may have a big test pile to cope with this problems, anyway;"<br /><br />Here's what we have done in LedgerSMB about this issue:<br />1) Every table has a primary key which is a natural key.<br />2) Every table has a secondary, surrogate key used for joins.<br /><br />"I also find very difficult to write SQL in a way I don't have to "repeat myself" every time I need to embed a piece of knowledge in a statement, like putting a derived value expression in many queries."<br /><br />User defined functions can be used to do value derivation, and you don't lose any planner optimization when one doesn't have to hit a table.<br /><br />For example, in our fixed asset depreciation workflow, we have a function which calculates, on a straight-line basis, the amount to depreciate based on inputs, and we call this in the field list of the function. This way we can have several different straight line methods which vary in how time units are handled, but which share basic calculation code. This has no optimizer penalty because the function is immutable (depending solely on inputs) and does not hit underlying tables, so opacity is not a concern.<br /><br />Views can sometimes (but not always) solve some of this because views, unlike functions, are transparent to the planner at least in PostgreSQL, so the planner issues which get introduced end up being due to the output of the rewritten query with the view used instead of opacity issues. In other words, at least in PostgreSQL, the performance issues come from the fact that views are developer-opaque, as they are not planner-opaque.<br /><br />The above solutions don't cover all of the cases where one might wish to centralize code for maintainability reasons, but they help significantly.<br /><br />"And yes, NoSQL may be another more Cloud friendly avenue, but needs taking a much bigger risk for an ERP writer. Maybe a Saas solution like ours may benefit and I'm thinking about this, but is a complete rewrite and needs a complete architectural rethinking of an ERP system."<br /><br />I don't think NoSQL is viable in the ERP space because it is extremely inflexible regarding reporting. Where NoSQL really shines is where you have single-purpose applications which have their own integration API and extremely limited reporting needs. The example that comes to my mind would be an LDAP directory. While ERP applications could meet the first prong, I do not think they could ever meet the second. Indeed, while NoSQL systems may have a place in an ERP environment, I believe those would be entirely secondary to the RDBMS (i.e. perhaps data would be exported to such secondary systems).Chris Travershttps://www.blogger.com/profile/06211762965865744803noreply@blogger.comtag:blogger.com,1999:blog-3346090548501966296.post-87659779769231198362011-11-13T03:10:59.634-08:002011-11-13T03:10:59.634-08:00I'm the chief designer of a Saas ERP system in...I'm the chief designer of a Saas ERP system in Italy. Our design decision has been to develop an internal framework in Java, and in 2001, when we started, we developed our own ORM.<br />Today I can say that we have a wonderful development environment but I find the ORM sometime a performance limiting and complexity factor. ORMs don't necessarily make it difficult to do a clean relational design. We decided to design an ORM around the database design and not around an OO design, that's why we don't use Hibernate or something like it. But this made our OO design less than perfect. It's a trade-off.<br />I've been thinking about potential solutions for this problem and one may be your approach. Personally I find the following limitations in it:<br />- portability across RDBMSs. In our case portability has been a winning factor in one big project where we were forced to use MS SQL Server (...) instead of PostgreSQL;<br />- SQL is not a "db schema refactoring" friendly language, and in general is not a developer friendly language. Just think about the case of changing a primary key in a table (we don't use the "every PK is surrogate" pattern). All the joins in all SQL involving the table must be changed just for starters. Our framework today allows a change like this to happen nearly transparently, with full compiler checks. Normally SQL can't be checked statically. You may have a big test pile to cope with this problems, anyway;<br />- I also find very difficult to write SQL in a way I don't have to "repeat myself" every time I need to embed a piece of knowledge in a statement, like putting a derived value expression in many queries. I don't think views and functions can solve this kind of problems easily (that is without compromising the work done by the optimizer);<br />In the end what I'm thinking about is a DSL for database development that is at a higher level of abstraction than SQL but is translated at runtime into SQL. This means, instead of materializing the data model as programming language classes like we currently do with ORMs, and instead of using the database directly, like you do, we may have a middle tier intelligent interpreter that dynamically translates application requests into SQL (or stored procedures) using a sort of knowledge base about the model that is expressed mostly in a declarative way. Our framework today does a lot of this, but classes implementing the model are a bad design, in my experience, and I'm looking forward to get rid of them.<br />Maybe I will be able to start this project in a near future.<br />And yes, NoSQL may be another more Cloud friendly avenue, but needs taking a much bigger risk for an ERP writer. Maybe a Saas solution like ours may benefit and I'm thinking about this, but is a complete rewrite and needs a complete architectural rethinking of an ERP system.Luca Veronesehttps://www.blogger.com/profile/10889674744901410391noreply@blogger.com