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.
7
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.
The data table has a number of columns in it; one of the columns holds a type. A data_type table is keyed from the type and adds a name. An operation table is keyed by the name column of the data_type table and adds an action column.
1) I don’t see the point of the data_type table. Mapping a type to a name doesn’t really add value to this scenario. operation should be mapped directly to data.
2) The action column is the name of an operation to be carried out on every (new) row of data table that has the desired type. Is this simply a mapping exercise? Or do you frequently add new kinds of actions?
3) To what extent are operations reversible? Do you mean the entire collection of actions on a single data table row? Do you mean each individual action is reversible? Are you envisioning that transaction begin/commit would surround a set of actions on a single table? Or did you mean reversible on a larger scale, over minutes or days?
If your application layer is written in a modern object-oriented language, you have a Command Pattern. One facet of the command pattern is that you can add an Undo operation to the command.
Personal note: trying to make computations reversible when you have database triggers is an extremely hard thing to do. If you move consequences into the application layer, you will have some hope of being able to track the downstream activities of a data change and thus make the entire action or operation reversible.
2