金融交易系统数据库架构设计原则？
我想设计一个保存金融交易记录的数据库。我想将其设计为一个产品，以便它可以用于任何类型的金融交易。是否有一些特定于金融交易数据库设计的设计原则可以帮助我以最小的架构级别更改使数据库更加持久耐用。一些好的例子也会有很大的帮助。
谢谢
I want to design a database which will keep record for financial transaction.I want to design it as a product so that it can be used for any type of financial transaction.Are there some design principles specific to financial transaction database design that can help me out to make database more durable for long term with minimal architectural level changes.Some good examples will be a great help too.
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
金融系统特有的一些事情包括内部控制（这是一个关键的会计术语，做一些研究来真正思考这个问题）。输入支票值之类的事情也无法批准。例如使用存储过程而不是从应用程序生成的 SQL，这样您就可以将权限仅限于过程（在金融系统中根本没有动态 SQL），因此用户只能执行他们被授权执行的操作。除了生产 dba 和表的替代者之外，任何人都没有任何权利。欺诈是您试图保护系统免受外部攻击的行为。安全对于金融系统至关重要。
您还需要审核表来了解谁更改了哪些数据、何时更改以及旧值是什么。如果有人绕过内部控制（或系统忘记实施某些关键控制）窃取资金，这不仅是帮助发现问题的另一种方法，而且能够在无需恢复的情况下撤消错误通常也至关重要。一般而言，会计系统通常具有用户无法查看的数据字段，这些数据字段是通过默认值或以用户看不到的方式生成的。
另一件事是您需要及时查看操作，因此可能看起来像自然关系的事情可能需要非规范化以保留操作发生时的成本。因此，如果您有一个小时费率表，您可以使用它作为查找来获取操作时的费率，而不是加入它来获取查询时的费率。
金融系统中包含私人数据，几乎总是要考虑如何保护这些数据。您将需要加密和解密数据。您可能还需要加密备份。
这些数据是公司的命脉，拥有良好的备份计划和大量的恢复实践至关重要。异地备份至关重要。
数据完整性至关重要。您需要正确的数据类型，并且需要 pk/fk 关系、约束和触发器来执行规则。金融系统不能承受孤立记录的后果。
您需要非常仔细地考虑删除。财务系统经常进行软删除（将记录标记为已删除，以避免丢失历史数据。是的，XYZ 公司不再是客户，但您不想丢失他们过去拥有的订单的财务历史记录。我什至不会考虑在财务系统中使用级联删除，
不要只与会计师讨论设计系统，还要与将运行该系统的财务人员和审计结果的审计师交谈，并彻底了解您所在国家/地区已发布的会计标准。考虑一下数据仓库和归档数据的复杂性，
报告对于财务系统来说非常非常大。日常数据输入。
Some things particular to financial systems include internal controls (This is a critical accounting term, do some research to really think this one through). Things like the person entering the check value can't also approve it. Things like using stored procs and not SQL generated from the application so that you can restrict rights to only the procs (no dynamic SQL at all - ever - in a financial system) and so users can only do what they are authorized to do. No rights for anyone except the production dba and an alternate to the tables. Fraud is what you are trying to protect the system from not just outside attacks. Security is critical to financial systems.
You also need audit tables to know who changed what data and when and what the old value was. This is not only an additional way to help find problems if someone got around the internal controls (or the system forgot to implement some critical ones) stole money, but it is often critical to be able to undo a mistake without having to restore. In general accounting systems often have data fields that are not viewable by the user and that are generated through default values or in a way that the user doesn't see them.
Another thing is you need to view actions in time so things that might look like a natural relationship may need denormalizing to preserve what the cost was at the time the action happened. So if you have an hourly rate table, you would use that as a lookup to get the rate at the time of the action not join to it to get the rate when you query.
Financial systems have private data in them, almost always, think how you are going to protect this data. You will need to be encrypting and decrypting data. You probably want an encrypted backup as well.
This data is the lifeblood of a company, it is critical that you have a good backup plan and much practice restoring. Off-site backups are critical.
Data integrity is critical. You need the correct datatypes and you need pk/fk relationships, constraints and triggers to enforce the rules. A financial system can't afford to have orphaned records.
You need to consider deletes very carefully. Financial systems often do soft deletes (mark records as deleted to avoid losing historical data. Yes XYZ company is no longer a customer, but you don't want to lose the financial history of the orders they had in the past. I would not even consider using cascade delete in a financial system.
Don't just talk to accountants in designing the system, talk to financial people who will run the system and auditors who will audit the results. Read and know thoroughly the published accounting standard for the country you are designing for. Look at tax implications. This is complex stuff.
Think about data warehousing and archiving data. Financial systems often query old data for reports, reporting is big, big, big for financial systems. Think how to do it effectively without affecting day-to-day data entry.
根据您实际想要实现的目标，为了创建一个有用的“金融交易”系统，您需要自学日记账、分类账和其他会计细节。它并不像在表中记录实际交易那么简单......
真的，我认为您不会发现金融系统的数据库设计原则与任何需要其信息为 100 的数据库系统有很大不同％ 正确的。
因此，在使用数据库时阅读以下内容不会伤害任何人：
数据库设计最佳实践
您对数据库进行源代码控制吗？
应用程序开发人员犯的数据库开发错误
您最有用的数据库标准是什么？
Depending one what you are actually trying to achieve, for you to create a "financial transaction" system that is useful you will need to teach yourself about journals, ledgers and other details of accounting. It isn't as simple as logging the actual transactions in a table...
Really, I don't think you will find database design principles for financial systems that are all that different from from any database system that needs it's information to be 100% correct.
Hence, reading the following when working with databases never hurt anyone:
Database Design Best Practices
Do you source control your databases?
Database Development Mistakes Made by App Developers
What are some of your most useful database standards?