6 ms·

Ask HN: Finance industry devs, where do you put the business logic, DB or Code

20 points by ruslan_talpa 10y ago

If your answer is "majority in db" then how do you combat arguments like: * You can't test/debug BL in DB (or it's very hard) * You can't do continuous integration * The code a mess and hard to maintain/make sense of If your answer is "majority in app": * Do you have an API on top of DB that enforces the rules? * Is the security/data validation also done in the API/app layer?

usernamebias 10y ago
I am not in "Fintech", but I may be able to help with some background in solution architecture. A pattern that applies to most industries is Service-oriented Architecture (SOA) -- Where each layer (presentation, logic, data) is independent of each other. This allow for fantastic scalability and granular debugging.
- ruslan_talpa 10y ago
  Thank you but this is more of a "theoretical" answer and you don't really define what you mean by "logic" layer and how you implement it. Can you maybe give an example of a business rule (in finance) and where exactly will you implement it?
  - usernamebias 10y ago
    Sure thing. First, weirdly enough I prefer to call the Business logic layer just the Logic layer. This is just personal, for all intensive purposes its the Business logic. Goes without saying this example is basic and open for improvements. Let's suppose an application exists where a user can donate $10 a month and the app would randomly distribute it to charities of the users choice. -- Presentation layer, usually a public facing web application or mobile app will handle user interaction with said app. Here the pretty UI will make sure the user provides proper card information, charities etc. -- Logic layer, will be some API that will process the users information and distribute the money programmatically. -- Data layer, is some kind of relational database where data will can be persist if necessary. e.g User credentials. Standard security protocols such as form validation and sanitization should always be performed both at Presentation level and at Logic level. For compliance, Things like encryption at data rest and while its in transit are important. Method of communication between each layer would be critical as well. There is a whole LOT more things to consider so when thinking security. These are just an example.
    - ruslan_talpa 10y ago
      From high level i agree with your statement (about separating stuff) but when you say "logic layer", what prevents me from looking at it as a specific schema (in the sens of PostgreSQL) in the db (call it api) that has only stored procedures and views and the presentation layer has access only to that schema and not the underlying data? I don't have to move data across between layers (data<->logic) over the network.
cdnsteve 10y ago
All in app for this particular API, using CI, good test coverage. DB handles data integrity. App/API handles data validation.
- ruslan_talpa 10y ago
  Can you give a general description of that system, what's it's core functionality? Can you give an example of a "business rule" that you implemented in the app and a "data integrity rule" that you implemented in the db? Thank you
nostrademons 10y ago
Most hardcore finance shops (quants, hedge funds, algo traders) I know don't use a DB in the traditional SQL RDBMS sense. Proprietary time-series databases rule here - because microseconds count, it's very important to think about the access patterns of your data and optimize for the algorithms you're actually running.
- ruslan_talpa 10y ago
  Maybe i asked it wrong, I am referring more to the old fashioned banking industry, like accounts and things like that
- bbcbasic 10y ago
  Finance is broad. Outside of hft, RDBMS is a workhorse for back office data, metadata, powering websites etc.
schappim 10y ago
When doing consulting for an agency, I was managing the integration of a new iPhone app with a legacy government database. We were given access to their code and their database. One of my wise old developers frowned and when I asked why he told me there were literally hundreds of stored procedures in this database and no documentation. It was at this time he told me to when possible keep business logic in code so it can be managed by a version control, and easily replicated for CI. He advised the exception to this rule was when the stored procedure gave a large performance boost.
- ruslan_talpa 10y ago
  A few problems with your reply (but thank you for feedback) It's gov, not finance. The fact that there were hundreds and not documented does not mean it wasn't the right place for them to be implemented, can you say for sure that those hundreds of RPC (probably 10 lines each) would not turn into thousands of functions in the code (that can also be undocumented)
- ruslan_talpa 10y ago
  Maybe the number of functions would not increase much (if you ignore the ORM/framework) but their size i think would suffer because of all the additional code for turning rows to objects.
bbcbasic 10y ago
I've worked at finance places where a lot of BL is in the DB. Testing is a trade-off. You can test the end points of stored procedures that the app has access to for example. Although where I worked admittedly there was no automated testing. You can do CI if you have a way to deploy changes to scripts automatically. The code doesn't need to be a mess but admittedly PLSQL doesn't have the same nice abstraction patterns as OO so it does get a bit W.E.T. sometimes.
niftich 10y ago
> * You can't test/debug BL in DB (or it's very hard) You test/debug in a separate, isolated full-stack environment that has a copy of the data. > * You can't do continuous integration Correct. > * The code a mess and hard to maintain/make sense of This is conjecture, and it's entirely dependent on the quality of the code.
- ruslan_talpa 10y ago
  Thank you, short and to the point
- ruslan_talpa 10y ago
  About the code maintainability, can you elaborate a bit and share your experience? How many functions in the db? How do you namespace/separate them in modules? For the sake of the argument, if those functions would be moved to the app, would there be more or less of them, will they be more complex or less complex. I am particularly interested in this part since it seems to be a common theme/argument against BL in DB
  - niftich 10y ago
    Not fintech, but I dev and maintain some systems with business-logic-in-DB. We use Oracle, the language is PL/SQL. You get one level of namespacing, and function names have to be less than 32 characters, so abbreviated names are common. This can be unwieldy, but alleviated by good comments and docs. You want to code each function to do one thing: have a well-defined input, and a well-defined output. Living inside the database gives us transaction support for free -- atomic operations that either succeed or rollback. The application running inside the DB defines the external interface with its functions (the same way a web service or a library would), and keeps the raw tables as an implementation detail. If we moved more functions out of the app into a higher layer (ie. Java/Python/etc), we'd actually have more functions overall, as you'd either have to have a bridge method that calls the equivalent function inside the DB, or rely on an ORM and invent some instrumentation to deal with concurrency requirements [1] At the end of the day, a modern RDBMS allows you to implement applications that present a well-defined interface to the outside world, with the internal implementation remaining a private 'black box'. [1] https://docs.jboss.org/hibernate/orm/3.3/reference/en-US/html/transactions.html#transactions-optimistic https://docs.jboss.org/hibernate/orm/3.3/reference/en-US/htm...
antrix 10y ago
I've worked in finance technology for eight years now, in various domains. As a broad generalization, if you are latency bound (milliseconds or less) then everything is done in code and the DB is never in the main loop. At the other end, if you are throughput bound - think end of day/month activities - then stored procedures all the way. For everything in between, prefer to have the logic in code due to the maintenance benefits that come with it. > Do you have an API on top of DB that enforces the rules? In general, a database is owned by the app it is associated with and the app mediates all access to that data. The usual enterprise integration patterns (esb, messaging, web services, file dumps) are used to share data with other systems. > Is the security/data validation also done in the API/app layer? Largely yes. There's some level of security (like app access control and encryption at rest) that's handled in the DB tier but authorization is largely an app level concern. Similarly, some constraints are imposed in the DB schema but those are quite basic. Most validation is done in the app tier.
CyberFonic 10y ago
My experience is with insurance, funds management and telco billing systems. Although every project is different, as a general rule I prefer to use stored procedures to make changes to the data. The SPs are written to ensure data integrity. Of course, there are some very basic business rules evident at this level but the primary concern is the correctness of data as updated/inserted. I would characterise these as the "data integrity rules". Each SP encompasses a document, a test suite and the implementation. It goes through the DEV-TEST-UAT-PROD QA - promotion stepwise deployment. I see that as a reasonable substitute for continuous integration. The OO layer implements business rules. (The SPs are transactional/procedural). I consider the BRs as a lower level business logic. Then the application provides the high level business logic, e.g. validation, workflow, etc. In reference to your question, I generally end up with three layers, DB, BR (API), BL (app). Technically the DB also has an API, but that is only visible to the BR layer.
flukus 10y ago
I don't like a lot of logic in the the database, but you can do continuous integration with tools like flyway: https://flywaydb.org/ https://flywaydb.org/ In fact, it's pretty hard to do CI without tools like this, even with zero stored procs.

20 points by ruslan_talpa 10y ago

usernamebias 10y ago

I am not in "Fintech", but I may be able to help with some background in solution architecture. A pattern that applies to most industries is Service-oriented Architecture (SOA) -- Where each layer (presentation, logic, data) is independent of each other. This allow for fantastic scalability and granular debugging.

ruslan_talpa 10y ago
Thank you but this is more of a "theoretical" answer and you don't really define what you mean by "logic" layer and how you implement it. Can you maybe give an example of a business rule (in finance) and where exactly will you implement it?
- usernamebias 10y ago
  Sure thing. First, weirdly enough I prefer to call the Business logic layer just the Logic layer. This is just personal, for all intensive purposes its the Business logic. Goes without saying this example is basic and open for improvements. Let's suppose an application exists where a user can donate $10 a month and the app would randomly distribute it to charities of the users choice. -- Presentation layer, usually a public facing web application or mobile app will handle user interaction with said app. Here the pretty UI will make sure the user provides proper card information, charities etc. -- Logic layer, will be some API that will process the users information and distribute the money programmatically. -- Data layer, is some kind of relational database where data will can be persist if necessary. e.g User credentials. Standard security protocols such as form validation and sanitization should always be performed both at Presentation level and at Logic level. For compliance, Things like encryption at data rest and while its in transit are important. Method of communication between each layer would be critical as well. There is a whole LOT more things to consider so when thinking security. These are just an example.
  - ruslan_talpa 10y ago
    From high level i agree with your statement (about separating stuff) but when you say "logic layer", what prevents me from looking at it as a specific schema (in the sens of PostgreSQL) in the db (call it api) that has only stored procedures and views and the presentation layer has access only to that schema and not the underlying data? I don't have to move data across between layers (data<->logic) over the network.

cdnsteve 10y ago

All in app for this particular API, using CI, good test coverage. DB handles data integrity. App/API handles data validation.

ruslan_talpa 10y ago
Can you give a general description of that system, what's it's core functionality? Can you give an example of a "business rule" that you implemented in the app and a "data integrity rule" that you implemented in the db? Thank you

nostrademons 10y ago

Most hardcore finance shops (quants, hedge funds, algo traders) I know don't use a DB in the traditional SQL RDBMS sense. Proprietary time-series databases rule here - because microseconds count, it's very important to think about the access patterns of your data and optimize for the algorithms you're actually running.

ruslan_talpa 10y ago
Maybe i asked it wrong, I am referring more to the old fashioned banking industry, like accounts and things like that
bbcbasic 10y ago
Finance is broad. Outside of hft, RDBMS is a workhorse for back office data, metadata, powering websites etc.

schappim 10y ago

When doing consulting for an agency, I was managing the integration of a new iPhone app with a legacy government database. We were given access to their code and their database. One of my wise old developers frowned and when I asked why he told me there were literally hundreds of stored procedures in this database and no documentation. It was at this time he told me to when possible keep business logic in code so it can be managed by a version control, and easily replicated for CI. He advised the exception to this rule was when the stored procedure gave a large performance boost.

ruslan_talpa 10y ago
A few problems with your reply (but thank you for feedback) It's gov, not finance. The fact that there were hundreds and not documented does not mean it wasn't the right place for them to be implemented, can you say for sure that those hundreds of RPC (probably 10 lines each) would not turn into thousands of functions in the code (that can also be undocumented)
ruslan_talpa 10y ago
Maybe the number of functions would not increase much (if you ignore the ORM/framework) but their size i think would suffer because of all the additional code for turning rows to objects.

bbcbasic 10y ago

I've worked at finance places where a lot of BL is in the DB. Testing is a trade-off. You can test the end points of stored procedures that the app has access to for example. Although where I worked admittedly there was no automated testing. You can do CI if you have a way to deploy changes to scripts automatically. The code doesn't need to be a mess but admittedly PLSQL doesn't have the same nice abstraction patterns as OO so it does get a bit W.E.T. sometimes.

niftich 10y ago

> * You can't test/debug BL in DB (or it's very hard) You test/debug in a separate, isolated full-stack environment that has a copy of the data. > * You can't do continuous integration Correct. > * The code a mess and hard to maintain/make sense of This is conjecture, and it's entirely dependent on the quality of the code.

ruslan_talpa 10y ago
Thank you, short and to the point
ruslan_talpa 10y ago
About the code maintainability, can you elaborate a bit and share your experience? How many functions in the db? How do you namespace/separate them in modules? For the sake of the argument, if those functions would be moved to the app, would there be more or less of them, will they be more complex or less complex. I am particularly interested in this part since it seems to be a common theme/argument against BL in DB
- niftich 10y ago
  Not fintech, but I dev and maintain some systems with business-logic-in-DB. We use Oracle, the language is PL/SQL. You get one level of namespacing, and function names have to be less than 32 characters, so abbreviated names are common. This can be unwieldy, but alleviated by good comments and docs. You want to code each function to do one thing: have a well-defined input, and a well-defined output. Living inside the database gives us transaction support for free -- atomic operations that either succeed or rollback. The application running inside the DB defines the external interface with its functions (the same way a web service or a library would), and keeps the raw tables as an implementation detail. If we moved more functions out of the app into a higher layer (ie. Java/Python/etc), we'd actually have more functions overall, as you'd either have to have a bridge method that calls the equivalent function inside the DB, or rely on an ORM and invent some instrumentation to deal with concurrency requirements [1] At the end of the day, a modern RDBMS allows you to implement applications that present a well-defined interface to the outside world, with the internal implementation remaining a private 'black box'. [1] https://docs.jboss.org/hibernate/orm/3.3/reference/en-US/html/transactions.html#transactions-optimistic https://docs.jboss.org/hibernate/orm/3.3/reference/en-US/htm...

antrix 10y ago

I've worked in finance technology for eight years now, in various domains. As a broad generalization, if you are latency bound (milliseconds or less) then everything is done in code and the DB is never in the main loop. At the other end, if you are throughput bound - think end of day/month activities - then stored procedures all the way. For everything in between, prefer to have the logic in code due to the maintenance benefits that come with it. > Do you have an API on top of DB that enforces the rules? In general, a database is owned by the app it is associated with and the app mediates all access to that data. The usual enterprise integration patterns (esb, messaging, web services, file dumps) are used to share data with other systems. > Is the security/data validation also done in the API/app layer? Largely yes. There's some level of security (like app access control and encryption at rest) that's handled in the DB tier but authorization is largely an app level concern. Similarly, some constraints are imposed in the DB schema but those are quite basic. Most validation is done in the app tier.

CyberFonic 10y ago

My experience is with insurance, funds management and telco billing systems. Although every project is different, as a general rule I prefer to use stored procedures to make changes to the data. The SPs are written to ensure data integrity. Of course, there are some very basic business rules evident at this level but the primary concern is the correctness of data as updated/inserted. I would characterise these as the "data integrity rules". Each SP encompasses a document, a test suite and the implementation. It goes through the DEV-TEST-UAT-PROD QA - promotion stepwise deployment. I see that as a reasonable substitute for continuous integration. The OO layer implements business rules. (The SPs are transactional/procedural). I consider the BRs as a lower level business logic. Then the application provides the high level business logic, e.g. validation, workflow, etc. In reference to your question, I generally end up with three layers, DB, BR (API), BL (app). Technically the DB also has an API, but that is only visible to the BR layer.

flukus 10y ago

I don't like a lot of logic in the the database, but you can do continuous integration with tools like flyway: https://flywaydb.org/ https://flywaydb.org/ In fact, it's pretty hard to do CI without tools like this, even with zero stored procs.