+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: Should relationships be set in the DB or rely on code? (/showthread.php?tid=91046)
Should relationships be set in the DB or rely on code? - spreaderman - 06-10-2024
I am using mysql. I can optionally add hard coded relationships between tables in mysql by use of foreign key and make it cascade or restrictive for example. My question is, design wise, would it be bad to rely on the code only without setting the relationships in mysql? For example, below code doesn't necessarily need hard relationships in mysql to work.
PHP Code:
$post = $this->join('post_files', 'post_files.post_id = posts.id AND post_files.primary = `Y`', 'inner')
->join('post_images', 'post_images.post_id = posts.id AND post_images.primary = `Y`', 'inner')
->join('post_descriptions', 'post_descriptions.post_id = posts.id AND post_descriptions.primary = `Y`', 'inner')
->find($id);
RE: Should relationships be set in the DB or rely on code? - kenjis - 06-10-2024
In my opinion, relying on the code only is not recommended.
An answer by Claude 3 Sonnet:
Relying solely on application code to enforce relationships and constraints between database tables, without defining foreign key constraints and relationships directly in the database, is generally considered a less robust and less maintainable approach. Here are some considerations:
1. **Data Integrity**: One of the main purposes of using foreign key constraints in the database is to maintain data integrity. Without these constraints, it is possible for your application code to introduce inconsistent or invalid data relationships, which can lead to data corruption and anomalies.
2. **Performance**: Database management systems (DBMSs) like MySQL are optimized for enforcing constraints and relationships efficiently. Performing these checks and validations in application code can be less efficient, especially as the data volume grows.
3. **Maintainability**: Defining relationships and constraints at the database level makes the schema more self-documenting and easier to understand for developers working with the system. If these relationships are scattered across application code, it can become harder to maintain and reason about the data integrity rules.
4. **Portability**: If you decide to switch to a different database system or use a different application framework in the future, the application code that enforces relationships may need to be rewritten, while database-level constraints are more portable and independent of the application code.
5. **Atomicity and Transactions**: Database systems provide robust transaction management capabilities, ensuring that operations involving multiple tables and relationships are either fully committed or rolled back in case of failures. Enforcing relationships solely in application code can make it more challenging to maintain atomicity and consistency across multiple operations.
However, there may be scenarios where you need to complement database-level constraints with application-level checks, especially for more complex business rules or validation logic that cannot be easily expressed using database constraints.
In general, it is recommended to define foreign key constraints and relationships directly in the database schema whenever possible. This ensures data integrity, leverages the database system's optimizations, and promotes better maintainability and portability of your application. Application code can then focus on enforcing additional business rules and validations that cannot be expressed through database constraints alone.
RE: Should relationships be set in the DB or rely on code? - warcooft - 06-10-2024
It is recommended to add relationships to each related table so that when deleting data or updating data it will automatically trigger the child table. If without this relationship it is very troublesome, imagine if you have dozens of derived tables then you have to hard code it to delete all related data table.
RE: Should relationships be set in the DB or rely on code? - spreaderman - 06-10-2024
Many thanks for all the input. It is very clear. Don't rely purely on the code.