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.