3

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:

Trivial scheme

  • 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.

  • 4
    The fact you have complex business logic, yet you start with database is terrible idea. You should write the logic first and create the persistence after that. – Euphoric Feb 20 '14 at 21:22
  • 1
    @Tez depends very much on the type of processing that is required. Is it suited to database, or is it suited to application layer? – Kirk Broadhurst Feb 20 '14 at 21:24
  • If the result of each operation is stored in another table, what does it mean that "the operation should be reversible"? What is the cost of processing a single record? Estimate in milliseconds or fortnights or whatever is appropriate. – BobDalgleish Feb 20 '14 at 21:51
  • @Euphoric this is the brainstorming phase, also a part of db already exists from previous implementation. – Tez Feb 20 '14 at 22:41
  • @KirkBroadhurst as mentioned, an operation will generally solve math problem and store result(s) back in db, so I guess both db and app.layer are suitable. – Tez Feb 20 '14 at 22:41
4

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.

2

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.

  • 1) Well, this is only a partial structure. Table data_type will hold other columns and keys linked to other tables. – Tez Feb 22 '14 at 15:55
  • 2) actions will be added on a yearly basis, some even disabled, but not significant number, ie. up to 50. Actions will not be performed on a new data row immediately, yet on a daily basis as an automated task. 3) the answer to first question is yes, so only the collection of data_type operations performed on a certain row. Considering the larger scale, reversing should be triggered by user at any point. – Tez Feb 22 '14 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.