What are the scalability issues when using phpmyadmin

Should I define the relationships between tables in the database or just in code?

TL; DR: Relationship constraints should be in the database.


Your application is not big enough.

Indeed, you are right that forcing relationships between databases may require forcing relationships in the application.

However, I want to warn you that you should first check the documentation for the database software you are using and the product offerings that exist. For example, there are clustering offers in addition to Postgres and MySQL.

And even if you end up with one Validation need in the application, do not throw the baby away with the bath water =. Because the less you have to do, the better off you will be.

If you are concerned about future scalability issues, I fear that your application will have to undergo significant changes before it can scale anyway. As a rule of thumb, every time you wax 10x you will have to redesign. So let's not spend too much money not anticipating scalability issues and instead use money to actually get to the point where you have those issues.

Your application is not correct enough.

What is the likelihood that the database you are using has a bad implementation of the check compared to the likelihood that Your application has an incorrect implementation of the test?

And which one do you change most often?

I would bet the database is correct all the time.

Your developers don't think they're spread out enough.

Reduce the network round trip to the application when inserting / updating data as the application has to make more queries to verify data integrity.

Red flag!1

When you think:

  • check if the record exists
  • if not, insert data record

then you have failed the most fundamental problem of parallelism : Another process / thread may add the record while you continue.

When you think:

  • check if the record exists
  • if not, insert data record
  • check whether the record has been inserted as a duplicate

then you have MVCC not taken into account : The view of the database you have is a snapshot at the time you started your transaction. it shows Not any updates that occur and may not even be committed.

Maintaining constraints across multiple sessions is a very difficult problem. Be glad it is solved in your database.

1 Unless your database properly implements the Serializable property. but few actually do.


Latest:

And I think data integrity in the application will result in a more verbose error message than using a database. Example: If you are creating an API server. If you define relationships in the database and an error occurs (for example, the referenced entity does not exist), an SQL exception is displayed with a message.

Do not parse any error messages If you are using a production quality database, structured errors should be returned. They at least have an error code to indicate what might be wrong, and based on that code, you can generate an appropriate error message.

Note that most of the time the code is sufficient: if you have an error code telling you that a referenced foreign key does not exist, this table likely only contains one foreign key so that in the code you will know what the problem is.

And, let's face it, most of the time you won't be treating mistakes that elegantly anyway. Just because there are so many of them and you can't explain them all ...

... what about the point above correctness related. Anytime you see a "500: Internal server error" because a database restriction was triggered and not handled, it means that the database saved you because you just forgot to handle it in your code.