Clever Geek Handbook

Where should I manage my database connectivity?

I would like to understand the advantages / disadvantages of managing my database connectivity in code, rather than using SQL functions.

Just to explain: Coherence means that if I have users and every user has albums and every album has pictures, then deleting a user means deleting (or soft deleting) all albums and pictures. It can also mean having images with sequential IDs for each user and each album on top of their primary keys.

The code implies the use of "on inserted, deleted, updated" events in my codebase (eg "Eloquent")

Sql properties mean using cascade, trigger, etc.

People have complained that this opinion is based, but it is not. Applying only the rules of the code and no restrictions can lead to inconsistency if the mysql shell is used - it is a fact. Also, if anyone has links to best practices and links, that's fine too, I'm really curious about what the developers are doing on google, like: again.not.opinions.but.facts.

Edit: spelling, formatting, call for reopening based on the actual wording of the question.

+3


source to share


4 answers


Laravel's answer

Since you tagged this question laravel and mysqlI'm going to give you an answer that supports the Laravel development style. Laravel supports Eloquent ORMs and database migrations through Artisan. This will allow you to keep this "coherence" within your code, not strictly in the database.

ORM

There are many resources out there for understanding what an ORM is and how it works. Laravel uses Eloquent and allows you to enforce both relationships and rules for your models and their elements.

Migration

Using migrations , we can build our database in a way that makes sense with our models, while keeping it portable across most databases (MySQL vs. Postgres).



Multi-developer apps

Applications developed by more than one developer must be in sync. If one developer decides to add a table with specific restrictions, this should be passed on to the rest of the team. Adding rules directly to the database can lead to problems communicating with other team members and cause headaches. Instead, use migrations and ORMs to describe what has changed and why. Migrations will allow any other developer to simply "migrate" their database to the newest version and stay in sync.

Conclusion

To compare points with some of the other listed answers:

  • All codes are still in one place. Migrations and models are built in PHP and stored in your repo project.
  • It is already being applied in the application thanks to two DB rules from ORM and Migrations.
  • Practical as there is a lot of documentation and help on these topics in the Laravel community, and perhaps this is the "official" method for doing this.
  • KISS This will keep things simple, especially if you are dealing with other developers and environments as it is very portable.
+2


source


It depends, but here you have ideas:

  • Logic hosted in DB
    • Easier for developers (you put it on the DB and forget)
  • Logic placed in code
    • If you want to be purely object oriented, the object must have all the business logic.
    • If in the future you change the resilience engine (to an object oriented DB, for example, you need this logic in your code)
  • Posted in both:
    • For me the best solution is because putting it in the DB is low cost and for me OO coding will not go well with persistence (ORM).


Jordi Martinez

+2


source


This is a very controversial topic. I have a completely different opinion like most other developers (as seen above). It might be worth checking this out:

I will always coordinate all my data within the application code and not use foreign keys or other constraints.

Here are the reasons:

  • One code location . All my logic should be in one place. If you spread logic to more than one place, developers will tend to forget about them in the future, and this will seriously damage the consistency of the database. For the same reason why I will never store btw procedures.

  • Still needed in the app . If you are doing consistency checks on your database, you still have to check the same constraints in your application. You need to do this so that the application can display pleasant errors to the user and enforce logical constraints. Therefore, if you need them, why repeat the same rules in the db again? This leads to redundant logic, resulting in easily forgotten logic, resulting in distorted coherence.

  • extremely impractical Ever tried to dump a database and then reimport to your database? It will be hard for you. Tables often violate constraints during import, different systems do not allow to ignore constraints during import. And administering data fixes on the fly via a database tool can also be a major pain. Therefore, it seriously affects the performance of your administrator.

Yes, there can be reasons to use constraints anyway, but they have to be really strong reasons if they can counter the three points.

+1


source


It is generally best to use foreign key constraints such as cascade. It's simple, effective, and consistent.

Something like this in the album table

FOREIGN KEY (uid) REFERENCES User(id) 
ON DELETE CASCADE

This will delete all records in the album table that reference a specific "id" in the user table, as soon as the "id" row in the user table is deleted.

0


source






More articles:


All Articles