We are currently working on a project that heavily relies on a database.
Among many tables the main focus is on table "data" which is linked to another table "data_type" as many-to-one, which is then linked to table "data_operation" as one-to-many.
The last table defines specific set of operations that has to be processed for each row in table "data" based on specific data type. The operation is processed against specific fields in table "data" and partially data from other tables, not mentioned in this example. The actual operation is mostly a complex calculation or specific formula. The result of a specific operation will be stored in yet another table.
So in general we have:
- Projection for table "data" is approximately one million rows per year, while other tables should not change drastically on a yearly basis, but it will initially hold a few thousand rows, that is, each data type will define roughly 10-15 operations.
- Each operation should be reversible (revert changes).
- Processing speed is a very important factor.
- The application will most likely process 2500 new table "data" rows per day.
My question is concerning the best approach to implement operations.
Do you think it is wiser to move business logic and rules to a database (procedures, triggers for each operation) or implement and process each operation in the application/business layer? What would be the ideal generic structure?
Also I am open for other approaches as well.
Best Answer
I'm not sure why euphoric didn't post his comment as an answer but he's correct. The same data might show up in many use cases and that will impact your rules. You should design your business classes for each use case based in the expected behavior. Then you can look at what data you have and figure out how to store it.
For example you might be able to save a partial quote. You may require more data though before the system allows a quote to be sent to a customer. Yet different rules for the quote to be converted to an order, etc.