There are four main open-source relational database management systems of note that might be appropriate to this sort of application: Postgresql, MySQL, Firebird and Ingres. There are other systems such as SQLite, but they do not have this type of architecture and are not really designed for this type of workload. Some other open-source database management systems of this type do exist, but do not appear to be strongly viable for some reason, such as a lack of apparent vendor commitment. An example of a system that has this type of issue is SAP-DB.
Postgresql has the best feature set of any of the open-source databases, and support for XA transactions, which you will probably want if your application is a three-tier system and supports transactions of non-trivial complexity. In particular you will want this if you want to do transactions spanning more than one call to the database.
Several commercial variants of PostgreSQL have been built over the years, such as Illustra,Greenplum and EnterpriseDB. Illustra was a commercial release of PostgreSQL which was subsequently bought by Informix. Greenplum is a mofified version designed for data warehousing applications. EnterpriseDB is a company that provides supported commercial versions of PostgreSQL with some value-added software.
MySQL 5.x has a feature set that supports a reasonable cross section of capabilities, but it is not as feature-rich as PostgreSQL. It has more widespread mainstream acceptance and would be the easiest of the open-source database management systems to recruit skilled developers for. Although older versions did not have robust transaction support, transactional storage engines such as InnoDB have been available for some time. The currentpoliticssurrounding the acquisition by Sun have generated code forks and the MySQL landscape is somewhat messy, with controversy about quality issues in the 5.1 release. However, MySQL is by far the most popular and best known of the open-source database management systems and is the only one with significant brand recognition outside of open-source circles.
Firebird is an open-source version of Interbase. Last I looked, it did not have XA support but would be fine if your application was set up as a two-tier client-server system. Update: I can't find a definitive specification on this, but the documentation does indicate that it has support for two-phase commit, but what I could find was not specific on whether it supported the XA protocol. The documentation implies that the JDBC driver does have support for two-phase commits.
An interesting variant on this system is Fyracle, which is designed to offer a degree of compatibility with Oracle. This was originally developed for use as a back-end to Compiere, which was built against Oracle and quite tightly coupled to it.
Ingres is now available with an open-source license, but has been greeted with a bit of a collective yawn by the open-source community. However It is quite feature-rich and very mature - I know people who were doing INGRES apps in 1990, and it dates back to the 1980s.
话虽如此，如果您担心 SQL Server 许可成本，我会首先推荐 PostgreSQL，然后再推荐 MySQL 作为您选择的数据库。
My advice? Don't. Better to buy one. People who know more about accounting have written good packages that already deal with GAAP. They have a larger user base than you'll ever have, which will uncover defects faster. This is a classic "buy versus build". There's no competitive advantage to your firm by writing their own. If you're doing it because you're worried about license costs, I'd say you haven't accounted for the development time properly. That's the only way you could justify doing this in-house.
With that said, if you are worried about SQL Server licensing costs, I'd recommend PostgreSQL first or MySQL second as your database of choice.
I agree strongly with answers from duffymo and tuinstoel and others. Reconsider your build vs. buy decision. Let me tell you a story:
While I was working at a mid-sized company (international, >$100M/yr revenue), the CFO decided to replace the financial systems with Oracle Financials. Only that package didn't exactly match the accounting practices used by this company.
So the CFO hired a team of contract programmers, and paid them to customize Oracle Financials to the preferred accounting practices. She sunk 12 months of time, $1 million in programmer wages, plus the initial cost of the software, just to duplicate the accounting system that they had intended to replace.
She said if she had to do it over again, she would buy the commercial package, but adapt the company's accounting habits to the defaults supported by the software. That would be far easier, quicker, and more likely to succeed.
So consider the cost of building your own custom package. Also consider the ongoing cost to your company of maintanance, debugging, and enhancement for that software. Even if buy a six-figure commercial package, that'll probably be less expensive than paying the programmers to develop and maintain such a system.
To answer your stated question more directly, I don't think there's a significant difference between PostgreSQL and MySQL that is relevant to your project. Since you are comfortable with MySQL, you might as well go with that.
I would like to offer an obligatory reminder not to use inexact data types like FLOAT or DOUBLE PRECISION for financial data.
对于任何想要使用开源数据库的应用程序来说，毫无疑问的答案就是 Postgres。 它比 MySQL 更适合“企业级”，更不用说它更好地遵循 SQL 标准。 MySQL 在其后续版本中已经有了很大的改进，但 Postgres 仍然在各个方面都击败了它。
For any application that wants to use an open source database, the hands-down answer is Postgres. It's a LOT more "enterprise-ready" than MySQL, not to mention that it follows the SQL standard a lot better. MySQL has improved a lot with its later versions, but Postgres still beats it in every category.
对于您的应用程序来说，这并不重要。 从 sqlite 到 MySQL 再到 Postgres 的任何东西都可能工作得很好。 选择您最熟悉的一个。
For your application, it won't really matter. Anything from sqlite to MySQL to Postgres would probably work just fine. Pick the one you're most familiar with.
Honestly, any of the usual suspects will do the job. Maintaining the chart of accounts and related data tables is the root issue that drove most all of the relational model. In fact, if you think about the general journal view of an accounting system, all you have is the chart-of-accounts and the general journal, composed of transaction number, date, description, debit account and amount, credit account and amount. Everything else you do is a SELECT on those.
That said, though, there are so many perfectly adequate, well-tested and accepted financial packages, including open-source free (as in beer) versions, that unless you mean it for an etude, a study project, I'd put my effort into googling and selecting one.
Saw your update. The thing is, this is an issue that's mostly going to be determined by nonfunctional requirements. Are you going to distribute the database across more than one server? how much load do you expect? Transactions per second or transactions per day? I've built systems around both in the last couple of years, and it's usually reliability and avilability requirements that are the most decisive: PostgreSQL deals with concurrent updates of a single row more effectively, by enforcing row atomicity and serializing concurrent updates. On the other hand, MySQL seems to deal with really large databases better. Yet a third question is backup --- one of them (I don't recall which one right now) more or less requires some down time to backup.
对于内部基于 Web 的会计应用程序，您可能最好使用 Gemstone 作为免费但非开源的对象数据库，并使用 Seaside 作为 Web 框架。 也称为玻璃。
对于内部应用程序，您的开发人员工作量将受到限制。 Gemstone 作为一个smalltalk 镜像，提供了迄今为止最好的开发人员生产力。 它支持在更改对象定义时迁移对象，从而实现真正的迭代开发。 Seaside 用精心设计的领域特定语言取代了模板来构建 Web 应用程序。
For an in-house web-based accounting application, you might be better off with Gemstone as a free but not open source object database and Seaside as the web framework. Otherwise known as GLASS.
For an in-house application, you're going to be limited in developer effort. Gemstone, as a smalltalk image, provides the best developer productivity by far. Its support for migrating objects when changing their definition allows real iterative development. Seaside replaces templates by a well-designed domain-specific language for building web applications.
I build accounting software on PostgreSQL. It works very well. I would highly recommend it. In fact (shameless plug), you might consider working with us to improve our project and using it as a jumping off point.
There are a couple reasons in particular:
LISTEN/NOTIFY gives you an ability to hook other programs into your accounting db when something changes without having to check tables every so often.
We've found extremely good performance with most complex queries.
Firebird and Ingres will give you a very rock-solid relational solution. MySQL I wouldn't recommend because you are really tying everything to one app only that can write to the db (sql mode soup means relations are basically a private API instead of the public API they are in PostgreSQL, Firebird, and Ingres), and this means less flexibility down the road.
However, with PostgreSQL, you get a top-notch, extensible development platform in a box. Pace of development is high. It is rock solid. The advanced features are very helpful. You won't be disappointed. We haven't been.
If this is a desktop application you might want to look at SQLite. It's very well known, in the public domain, and not terribly difficult to work with.
Since you don't need stored procedures, take that off your list.
You'll be a lot happier putting business logic in code, not in the database. If you have the chance to start "clean", then use the database for what makes the most sense -- persistence not processing.
Once you make that decision, the subtle differences between MySQL and PostgreSQL go away. Both are relational engines that handle nearly identical SQL. Focus on the things they do best.
Recommendation: Make your application independent of any database peculiarities.
发布评论
评论（13）
有四种主要的开源关系数据库管理系统可能适合此类应用程序：Postgresql、MySQL、Firebird 和 Ingres。 还有其他系统，例如 SQLite，但它们没有这种类型的架构，并且并不是真正为此设计的工作负载类型。 这种类型的其他一些开源数据库管理系统确实存在，但由于某种原因（例如缺乏明显的供应商承诺）似乎不具有很强的可行性。 存在此类问题的系统示例是 SAP-DB。
Postgresql 拥有所有开源数据库中最好的功能集，并且支持 XA 事务，如果您的应用程序是您可能需要的是一个三层系统，支持非常复杂的事务。 特别是，如果您想要执行跨越多个数据库调用的事务，您将需要这一点。
多年来，已经构建了 PostgreSQL 的多个商业变体，例如 Illustra、 Greenplum 和 EnterpriseDB。 Illustra 是 PostgreSQL 的商业版本，随后被 Informix 收购。 Greenplum 是专为数据仓库应用程序设计的修改版本。 EnterpriseDB 是一家提供受支持的 PostgreSQL 商业版本以及一些增值软件的公司。
MySQL 5.x 有一个功能集，支持合理的跨部分功能，但它并不像功能那样 -与 PostgreSQL 一样丰富。 它具有更广泛的主流接受度，并且将是最容易招募熟练开发人员的开源数据库管理系统。 尽管旧版本没有强大的事务支持，但诸如 InnoDB 之类的事务存储引擎已经可用了一段时间。 当前 政治 周边被Sun收购产生了代码分支，MySQL 的情况是 有点混乱， 存在关于 5.1 版本中存在质量问题。 然而，MySQL 是迄今为止最流行、最知名的开源数据库管理系统，并且是唯一一个在开源圈子之外具有显着品牌知名度的公司。
Firebird 是 Interbase 的开源版本。 最后我发现，它没有 XA 支持，但如果您的应用程序设置为两层客户端-服务器系统，那就没问题了。 更新：我找不到这方面的明确规范，但文档确实表明它支持两阶段提交，但我能找到的并不是具体说明它是否支持 XA 协议。 该文档暗示 JDBC 驱动程序确实支持两阶段提交。
该系统的一个有趣变体是 Fyracle，它旨在提供一定程度的兼容性甲骨文。 它最初是为了用作 Compiere 的后端而开发的，它是针对 Oracle 构建的，并且与它。
Ingres 现已提供开源许可证，但遭到了一些集体哈欠开源社区。 然而，它的功能相当丰富且非常成熟 - 我认识 1990 年开发 INGRES 应用程序的人，它的历史可以追溯到 20 世纪 80 年代。
There are four main open-source relational database management systems of note that might be appropriate to this sort of application: Postgresql, MySQL, Firebird and Ingres. There are other systems such as SQLite, but they do not have this type of architecture and are not really designed for this type of workload. Some other open-source database management systems of this type do exist, but do not appear to be strongly viable for some reason, such as a lack of apparent vendor commitment. An example of a system that has this type of issue is SAP-DB.
Postgresql has the best feature set of any of the open-source databases, and support for XA transactions, which you will probably want if your application is a three-tier system and supports transactions of non-trivial complexity. In particular you will want this if you want to do transactions spanning more than one call to the database.
Several commercial variants of PostgreSQL have been built over the years, such as Illustra, Greenplum and EnterpriseDB. Illustra was a commercial release of PostgreSQL which was subsequently bought by Informix. Greenplum is a mofified version designed for data warehousing applications. EnterpriseDB is a company that provides supported commercial versions of PostgreSQL with some value-added software.
MySQL 5.x has a feature set that supports a reasonable cross section of capabilities, but it is not as feature-rich as PostgreSQL. It has more widespread mainstream acceptance and would be the easiest of the open-source database management systems to recruit skilled developers for. Although older versions did not have robust transaction support, transactional storage engines such as InnoDB have been available for some time. The current politics surrounding the acquisition by Sun have generated code forks and the MySQL landscape is somewhat messy, with controversy about quality issues in the 5.1 release. However, MySQL is by far the most popular and best known of the open-source database management systems and is the only one with significant brand recognition outside of open-source circles.
Firebird is an open-source version of Interbase. Last I looked, it did not have XA support but would be fine if your application was set up as a two-tier client-server system. Update: I can't find a definitive specification on this, but the documentation does indicate that it has support for two-phase commit, but what I could find was not specific on whether it supported the XA protocol. The documentation implies that the JDBC driver does have support for two-phase commits.
An interesting variant on this system is Fyracle, which is designed to offer a degree of compatibility with Oracle. This was originally developed for use as a back-end to Compiere, which was built against Oracle and quite tightly coupled to it.
Ingres is now available with an open-source license, but has been greeted with a bit of a collective yawn by the open-source community. However It is quite feature-rich and very mature - I know people who were doing INGRES apps in 1990, and it dates back to the 1980s.
我的建议？ 不。 最好买一个。 对会计有更多了解的人已经编写了已经处理 GAAP 的优秀软件包。 他们拥有比您以往任何时候都更大的用户群，这将更快地发现缺陷。 这是典型的“购买与建造”。 自己编写代码不会给你的公司带来任何竞争优势。 如果您这样做是因为担心许可成本，那么我想说您没有正确考虑开发时间。 这是你在内部证明这样做的唯一方法。
话虽如此，如果您担心 SQL Server 许可成本，我会首先推荐 PostgreSQL，然后再推荐 MySQL 作为您选择的数据库。
My advice? Don't. Better to buy one. People who know more about accounting have written good packages that already deal with GAAP. They have a larger user base than you'll ever have, which will uncover defects faster. This is a classic "buy versus build". There's no competitive advantage to your firm by writing their own. If you're doing it because you're worried about license costs, I'd say you haven't accounted for the development time properly. That's the only way you could justify doing this in-house.
With that said, if you are worried about SQL Server licensing costs, I'd recommend PostgreSQL first or MySQL second as your database of choice.
我非常同意 duffymo 和 tuinstoel 等人的回答。 重新考虑您的构建与购买决定。 让我告诉你一个故事：
当我在一家中型公司（国际化，年收入超过 1 亿美元）工作时，首席财务官决定用 Oracle Financials 替换财务系统。 只是该包与该公司使用的会计惯例不完全匹配。
因此，首席财务官聘请了一个合同程序员团队，并付钱让他们根据首选的会计实践定制 Oracle Financials。 她花费了 12 个月的时间、100 万美元的程序员工资，加上软件的初始成本，只是为了复制他们原本打算更换的会计系统。
她说，如果必须重来一次，她会购买商业软件包，但会根据软件支持的默认设置调整公司的会计习惯。 这会更容易、更快，也更有可能成功。
因此，请考虑构建自己的定制包的成本。 还要考虑您的公司在维护、调试和增强该软件方面的持续成本。 即使购买六位数的商业包，也可能比支付程序员开发和维护这样一个系统的费用要便宜。
为了更直接地回答你提出的问题，我认为 PostgreSQL 和 MySQL 之间没有与你的项目相关的显着差异。 既然您对 MySQL 很熟悉，那么您也可以选择它。
我想强制提醒不要对财务数据使用不精确的数据类型，例如
FLOAT或DOUBLE PRECISION。I agree strongly with answers from duffymo and tuinstoel and others. Reconsider your build vs. buy decision. Let me tell you a story:
While I was working at a mid-sized company (international, >$100M/yr revenue), the CFO decided to replace the financial systems with Oracle Financials. Only that package didn't exactly match the accounting practices used by this company.
So the CFO hired a team of contract programmers, and paid them to customize Oracle Financials to the preferred accounting practices. She sunk 12 months of time, $1 million in programmer wages, plus the initial cost of the software, just to duplicate the accounting system that they had intended to replace.
She said if she had to do it over again, she would buy the commercial package, but adapt the company's accounting habits to the defaults supported by the software. That would be far easier, quicker, and more likely to succeed.
So consider the cost of building your own custom package. Also consider the ongoing cost to your company of maintanance, debugging, and enhancement for that software. Even if buy a six-figure commercial package, that'll probably be less expensive than paying the programmers to develop and maintain such a system.
To answer your stated question more directly, I don't think there's a significant difference between PostgreSQL and MySQL that is relevant to your project. Since you are comfortable with MySQL, you might as well go with that.
I would like to offer an obligatory reminder not to use inexact data types like
FLOATorDOUBLE PRECISIONfor financial data.对于任何想要使用开源数据库的应用程序来说，毫无疑问的答案就是 Postgres。 它比 MySQL 更适合“企业级”，更不用说它更好地遵循 SQL 标准。 MySQL 在其后续版本中已经有了很大的改进，但 Postgres 仍然在各个方面都击败了它。
For any application that wants to use an open source database, the hands-down answer is Postgres. It's a LOT more "enterprise-ready" than MySQL, not to mention that it follows the SQL standard a lot better. MySQL has improved a lot with its later versions, but Postgres still beats it in every category.
有开源免费会计系统。 就像 osFinancials 一样。 我实在不明白你为什么要自己搭建系统？
There are open source free accounting systems. Like osFinancials. I really can't understand why you want to build your own system?
对于您的应用程序来说，这并不重要。 从 sqlite 到 MySQL 再到 Postgres 的任何东西都可能工作得很好。 选择您最熟悉的一个。
For your application, it won't really matter. Anything from sqlite to MySQL to Postgres would probably work just fine. Pick the one you're most familiar with.
火鸟 - http://www.firebirdsql.org/
Firebird - http://www.firebirdsql.org/
如果您熟悉 MySQL，请使用它。
但选择正确的数据库引擎而不是默认的MyISAM
存储引擎列表
If you are familiar with MySQL then use it.
But select proper database engine instead of default MyISAM
List of Storage Engines
老实说，任何常见的嫌疑人都可以完成这项工作。 维护会计科目表和相关数据表是驱动大多数关系模型的根本问题。 事实上，如果您考虑会计系统的一般日记帐视图，您拥有的只是会计科目表和一般日记帐，由交易编号、日期、描述、借方帐户和金额、信用账户和金额。 你所做的其他一切都是对这些的选择。
尽管如此，有很多完全足够、经过充分测试和接受的财务包，包括开源免费（如啤酒）版本，除非您的意思是练习曲、研究项目，否则我'我花了很多精力在谷歌上搜索并选择一个。看到了你的更新。 问题是，这个问题主要由非功能性需求决定。 您打算将数据库分布在多个服务器上吗？ 您预计有多少负载？ 每秒交易数还是每天交易数？ 在过去的几年里，我围绕这两者构建了系统，通常可靠性和可用性要求是最具决定性的：PostgreSQL 通过强制行原子性和序列化并发更新来更有效地处理单行的并发更新。 另一方面，MySQL 似乎能更好地处理大型数据库。 第三个问题是备份——其中一个（我现在不记得是哪一个）或多或少需要一些停机时间来备份。
Honestly, any of the usual suspects will do the job. Maintaining the chart of accounts and related data tables is the root issue that drove most all of the relational model. In fact, if you think about the general journal view of an accounting system, all you have is the chart-of-accounts and the general journal, composed of transaction number, date, description, debit account and amount, credit account and amount. Everything else you do is a SELECT on those.
That said, though, there are so many perfectly adequate, well-tested and accepted financial packages, including open-source free (as in beer) versions, that unless you mean it for an etude, a study project, I'd put my effort into googling and selecting one.Saw your update. The thing is, this is an issue that's mostly going to be determined by nonfunctional requirements. Are you going to distribute the database across more than one server? how much load do you expect? Transactions per second or transactions per day? I've built systems around both in the last couple of years, and it's usually reliability and avilability requirements that are the most decisive: PostgreSQL deals with concurrent updates of a single row more effectively, by enforcing row atomicity and serializing concurrent updates. On the other hand, MySQL seems to deal with really large databases better. Yet a third question is backup --- one of them (I don't recall which one right now) more or less requires some down time to backup.
对于内部基于 Web 的会计应用程序，您可能最好使用 Gemstone 作为免费但非开源的对象数据库，并使用 Seaside 作为 Web 框架。 也称为玻璃。
对于内部应用程序，您的开发人员工作量将受到限制。 Gemstone 作为一个smalltalk 镜像，提供了迄今为止最好的开发人员生产力。 它支持在更改对象定义时迁移对象，从而实现真正的迭代开发。 Seaside 用精心设计的领域特定语言取代了模板来构建 Web 应用程序。
For an in-house web-based accounting application, you might be better off with Gemstone as a free but not open source object database and Seaside as the web framework. Otherwise known as GLASS.
For an in-house application, you're going to be limited in developer effort. Gemstone, as a smalltalk image, provides the best developer productivity by far. Its support for migrating objects when changing their definition allows real iterative development. Seaside replaces templates by a well-designed domain-specific language for building web applications.
我在 PostgreSQL 上构建会计软件。 它运作得很好。 我会极力推荐它。 事实上（无耻的插件），您可能会考虑与我们合作来改进我们的项目并将其用作起点。
特别是有几个原因：
Firebird 和 Ingres 将为您提供非常坚如磐石的关系解决方案。 MySQL 我不推荐，因为你实际上将所有内容都绑定到一个只能写入数据库的应用程序（sql 模式汤意味着关系基本上是一种私有 API，而不是 PostgreSQL、Firebird 和 Ingres 中的公共 API），并且这意味着未来的灵活性会降低。
然而，使用 PostgreSQL，您将获得一个一流的、可扩展的开发平台。 发展速度很快。 它坚如磐石。 高级功能非常有用。您不会失望的。 我们没有去过。
I build accounting software on PostgreSQL. It works very well. I would highly recommend it. In fact (shameless plug), you might consider working with us to improve our project and using it as a jumping off point.
There are a couple reasons in particular:
Firebird and Ingres will give you a very rock-solid relational solution. MySQL I wouldn't recommend because you are really tying everything to one app only that can write to the db (sql mode soup means relations are basically a private API instead of the public API they are in PostgreSQL, Firebird, and Ingres), and this means less flexibility down the road.
However, with PostgreSQL, you get a top-notch, extensible development platform in a box. Pace of development is high. It is rock solid. The advanced features are very helpful. You won't be disappointed. We haven't been.
如果这是一个桌面应用程序，您可能需要查看 SQLite。 它在公共领域是众所周知的，并且使用起来并不困难。
If this is a desktop application you might want to look at SQLite. It's very well known, in the public domain, and not terribly difficult to work with.
由于您不需要需要存储过程，因此请将其从列表中删除。
您会更高兴将业务逻辑放入代码中，而不是数据库中。 如果您有机会开始“干净”，那么将数据库用于最有意义的用途——持久性而不是处理。
一旦做出决定，MySQL 和 PostgreSQL 之间的细微差别就会消失。 两者都是处理几乎相同 SQL 的关系引擎。 专注于他们最擅长的事情。
建议：使您的应用程序独立于任何数据库特性。
Since you don't need stored procedures, take that off your list.
You'll be a lot happier putting business logic in code, not in the database. If you have the chance to start "clean", then use the database for what makes the most sense -- persistence not processing.
Once you make that decision, the subtle differences between MySQL and PostgreSQL go away. Both are relational engines that handle nearly identical SQL. Focus on the things they do best.
Recommendation: Make your application independent of any database peculiarities.