**Managing relationships between database tables (one-to-one, one-to-many)** In this topic, we will explore how to manage relationships between database tables in CodeIgniter. Understanding these relationships is crucial for building robust and scalable applications. We will cover the basics of one-to-one and one-to-many relationships, and provide practical examples to help you apply this knowledge in your own projects. **What are relationships between database tables?** In a relational database, tables are related to each other through various types of relationships. These relationships can be categorized into three main types: 1. **One-to-one (1:1)**: One record in one table is related to only one record in another table. 2. **One-to-many (1:N)**: One record in one table is related to multiple records in another table. 3. **Many-to-many (M:N)**: Multiple records in one table are related to multiple records in another table. **One-to-one relationships** In a one-to-one relationship, each record in one table is related to only one record in another table. This type of relationship is often used when you have a unique identifier in one table that corresponds to a single record in another table. Example: Suppose we have two tables: `users` and `user_profiles`. Each user has a unique identifier, and each user profile is related to only one user. ```sql CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(255) ); CREATE TABLE user_profiles ( id INT PRIMARY KEY, user_id INT, bio TEXT, FOREIGN KEY (user_id) REFERENCES users(id) ); ``` In CodeIgniter, we can establish this relationship using the `hasOne` method in our model: ```php // models/User.php class User extends CI_Model { public function __construct() { parent::__construct(); $this->load->database(); } public function getUserProfile($userId) { $this->db->select('*'); $this->db->from('user_profiles'); $this->db->where('user_id', $userId); $query = $this->db->get(); return $query->row(); } } ``` **One-to-many relationships** In a one-to-many relationship, one record in one table is related to multiple records in another table. This type of relationship is often used when you have a parent-child relationship between tables. Example: Suppose we have two tables: `orders` and `order_items`. Each order can have multiple order items. ```sql CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(id) ); CREATE TABLE order_items ( id INT PRIMARY KEY, order_id INT, product_id INT, quantity INT, FOREIGN KEY (order_id) REFERENCES orders(id) ); ``` In CodeIgniter, we can establish this relationship using the `hasMany` method in our model: ```php // models/Order.php class Order extends CI_Model { public function __construct() { parent::__construct(); $this->load->database(); } public function getOrdersForCustomer($customerId) { $this->db->select('*'); $this->db->from('orders'); $this->db->where('customer_id', $customerId); $query = $this->db->get(); return $query->result(); } public function getOrderItems($orderId) { $this->db->select('*'); $this->db->from('order_items'); $this->db->where('order_id', $orderId); $query = $this->db->get(); return $query->result(); } } ``` **Many-to-many relationships** In a many-to-many relationship, multiple records in one table are related to multiple records in another table. This type of relationship is often used when you have a relationship between two tables that has no direct one-to-one correspondence. Example: Suppose we have two tables: `categories` and `products`. Each category can have multiple products, and each product can be related to multiple categories. ```sql CREATE TABLE categories ( id INT PRIMARY KEY, name VARCHAR(255) ); CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(255) ); CREATE TABLE category_products ( category_id INT, product_id INT, PRIMARY KEY (category_id, product_id), FOREIGN KEY (category_id) REFERENCES categories(id), FOREIGN KEY (product_id) REFERENCES products(id) ); ``` In CodeIgniter, we can establish this relationship using the `belongsToMany` method in our model: ```php // models/Category.php class Category extends CI_Model { public function __construct() { parent::__construct(); $this->load->database(); } public function getProductsForCategory($categoryId) { $this->db->select('*'); $this->db->from('products'); $this->db->join('category_products', 'products.id = category_products.product_id'); $this->db->where('category_products.category_id', $categoryId); $query = $this->db->get(); return $query->result(); } } ``` **Practical Takeaways** * When establishing relationships between database tables, consider the type of relationship (one-to-one, one-to-many, many-to-many) and how it will impact your application. * Use the `hasOne`, `hasMany`, and `belongsToMany` methods in your models to establish relationships between tables. * Use joins and subqueries to retrieve data from related tables. * Consider using Eloquent's `belongsToMany` method to establish many-to-many relationships. **Exercise** Create a model that establishes a one-to-many relationship between the `users` and `user_profiles` tables. Use the `hasOne` method to retrieve a user's profile. **Comment or ask for help** If you have any questions or need further clarification on this topic, please leave a comment below.