4

I'm searching about how to disable triggers, and seems like it's not possible under MariaDB.

My problem is, how to manage then, when you import data. I have a database where almost all business logic is inside it with triggers and stored procs. Probably, there are records autogenerated by triggers and stored-procs that were filled later by a user, and I don't know how to manage this if I import a database dump, or importing a csv file for example.

I'm sure the problem is I'm not seeing the obvious, and there are some problem in my data model, but because I'm starting with this project, I'm in time to modify what is needed, knowing best practices to take into consideration.

My question is, there are some best-practices to take into consideration, when we want to implement the business logic of an application, inside the database?

I hope I explained correctly.

This is a little Example coming to my mind: On a deployed database, there are a record that will be created from a trigger, and later, this record, is modified by the user. When import an sqldump, because this records exists, sql dump will fail trying to inserting this existing recods.

3
  • 1
    First thing , writing your application's business logic into your database is not a good practice. Jul 24, 2016 at 13:12
  • You can write your business logic into your code, it will help others for understanding your Data Flow structure and your Business Logic. Jul 24, 2016 at 13:13
  • Disable triggers? Can't do it. A trigger should be written so that it is always valid. (Exception: replication.)
    – Rick James
    Jul 24, 2016 at 15:26

2 Answers 2

1

Best practice...

A database is a place to store data. It is the 'source of truth'.

The application is the place for * interpreting the data * "Business logic" * Formatting and pretty-printing of output * Cleansing the data as it is stored into the database.

It is often wise (especially in more complex systems) to have a "database layer" in which the "business" info is transformed to/from "database" info. On the database side, there is "INT" and "FLOAT"; on the business side, there are other terms for numeric things. The business may say "store this"; the layer may turn that into one INSERT, or split the columns across two tables, or normalization plus inserts, or INSERT .. ON DUPLICATE KEY UPDATE .., or INSERT IGNORE, or an INSERT plus augment a summary table, or ...

Yes, some of those things could be done with TRIGGERs and FOREIGN KEY "cascading constraints", etc. But I would rather put all the logic in a single place. I find triggers clumsy to code. I find FKs limted in capability.

3
  • Then, do you suggest, have the logic on database is a bad idea and I have to move outside to .. in this sample, PHP?
    – GiuDev
    Jul 24, 2016 at 16:37
  • Yes. Business logic in PHP. Database Layer, if built, as functions (maybe classes) in PHP.
    – Rick James
    Jul 24, 2016 at 16:41
  • 1
    Or if your thinking of having more than one application using the same business logic, like a mobile app, use a webservice or a business layer of some kind between the database and the application.
    – rhughes
    Jul 27, 2016 at 15:56
0

All the DBMS vendors suggest to create all the bussines logic in the database because that will asures the dependency to specific DBMS.

To my students I recommend to analyze each situation, new capabilites like column store or in memory structures help you to improve your app performance moving some logic to the database and moving less information between servers and client because you generate only the reults from the database, also the develop team can use the functions in the same way with the same security level.

But moving all the logic to the database in some cases because the database is not the right tool for the specific problem and you will end creating a bottle neck.

Every behavior are diferent depending the tools and the logic that you choice for your application for that reason you need to know what are the advantages and disadvantages of your DBMS, web server, and others components in order to take the right choice

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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