A key element of many web applications, relational databases offer an organised and effective method of managing data. It is essential to comprehend their tenets in order to construct websites with PHP and other programming languages.
Understanding Relational Database Design
Data is arranged and managed by a server called a Relational Database Management System (RDBMS). Tables, sometimes called relations, are the units into which data is organised in an RDBMS. A table can be thought of as being comparable to a spreadsheet.
Columns, also known as fields or characteristics, make up each table. Each column has a distinct name and a corresponding data type, such as string or integer. Rows also referred to as records or tuplesare where the actual data is kept. A single item or entity is represented by each row in a table, and every row in a particular table has the same qualities.
The database schema is the entirety of a database’s table designs. A schema is similar to a blueprint that displays the main keys, foreign keys, tables, and their columns.
Finding and connecting data both inside and between tables depends on keys.
- Within a single table, every row is uniquely identified by a primary key. The selected primary key value must be distinct for each record, whether it is past, present, or future.
- In one table, a foreign key is a column (or group of columns) that points to the primary key in another table. Two tables’ data associations are represented by foreign keys. The CustomerID in the Orders table, for instance, would be a foreign key referencing the CustomerID primary key in the Customers table in a Book-O-Rama database.
When interacting with relational databases, the standard language is called Structured Query Language (SQL). Data can be created, retrieved, updated, and deleted with it. Tables and other schema elements are created and modified using Data Definition Language (DDL), and data manipulation operations like INSERT, SELECT, UPDATE, and DELETE are performed using Data Manipulation Language (DML).
The real-world things you are modelling should be taken into consideration while creating a database for a web application. Using atomic column values, in which each cell contains a single value, avoiding the storage of unnecessary data, and using reasonable keys are all examples of good design concepts. Even if there are ideas like entity relationship diagrams and database normalisation, the typical web database is frequently not very complex. Database design is sometimes seen as an art.
Representing Relationships between Entities
Relational databases are based on relationships between data in distinct tables, which are represented by foreign keys. Relationships fall into three main categories: many-to-many, one-to-many, and one-to-one.
One-to-One Relationship: Involve one entity and one other. A customer row and an address row would have a one-to-one relationship, for instance, if the address of a client was kept in a different table from the primary customer data. Adding a foreign key to one table that references the other table’s primary key is the standard way to do this. Except in certain cases, data in a one-to-one relationship can frequently be kept in a single table.
One-to-Many Relationship: One row in one table connects to many rows in another. A typical scenario is when a customer places several orders; a single consumer may have numerous orders. A foreign key in the table on the “many” side of these relationships points to the main key of the table on the “one” side. For example, a CustomerID foreign key referencing the Customers table would be present in the Orders table.
Many-to-Many Relationship: Many rows in one table are connected to numerous rows in another table in a many-to-many relationship. One book may be written by more than one author, and authors may write more than one book, for instance, if you have tables for books and authors. This kind of interaction typically calls for a third table, which is frequently referred to as a linking or junction table.
The main keys of the two tables in the many-to-many relationship are referenced by the foreign keys in this connecting table. Foreign keys for both ISBN (from Books) and AuthorID (from Authors) in pairs would be included in a Books Authors table for the Books and Authors example, indicating which authors authored which books. An further illustration would be the connection between Orders and Books, which is usually managed by an Order Items database that associates particular books with particular orders.
Representing Relationships with Code Examples (SQL)
These associations are defined using SQL CREATE TABLE commands, which create the database structure that PHP programs subsequently communicate with, as seen in the following examples.
One-to-Many Example (Customers and Orders): The main key in the Customers table is CustomerID. In addition to having a foreign key called CustomerID that connects to the Customers table, the Orders table also has its own primary key called OrderID.
-- Create the Customers table
CREATE TABLE Customers (
CustomerID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255),
Address VARCHAR(255),
City VARCHAR(100)
);
-- Create the Orders table with a foreign key referencing Customers
CREATE TABLE Orders (
OrderID INT AUTO_INCREMENT PRIMARY KEY,
CustomerID INT, -- Foreign key
Amount DECIMAL,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);According to this structure, a CustomerID may appear more than once in the Orders table (a single client may have several orders), but each OrderID in the Orders table is linked to a single CustomerID from the Customers table.
Many-to-Many Example (Books and Authors via a Linking Table):The primary key in the Books table is ISBN. AuthorID is the main key in the Authors table. Book_Authors, a connecting table, is made to illustrate the many-to-many relationship.
-- Create the Books table
CREATE TABLE Books (
ISBN VARCHAR(20) PRIMARY KEY,
Title VARCHAR(255),
Price DECIMAL(10, 2)
-- ... other book details
);
-- Create the Authors table
CREATE TABLE Authors (
AuthorID INT AUTO_INCREMENT PRIMARY KEY,
AuthorName VARCHAR(255)
-- ... other author details
);
-- Create the linking table for the Many-to-Many relationship
CREATE TABLE Book_Authors (
Book_ISBN VARCHAR(20), -- Foreign key referencing Books
Author_AuthorID INT, -- Foreign key referencing Authors
PRIMARY KEY (Book_ISBN, Author_AuthorID), -- Composite primary key
FOREIGN KEY (Book_ISBN) REFERENCES Books(ISBN),
FOREIGN KEY (Author_AuthorID) REFERENCES Authors(AuthorID)
);A book may have many Book_Authors entries (linked to numerous authors), and each author may have multiple entries (associated to various books), exhibiting the many-to-many relationship.
Even though these examples show how to construct a SQL database structure, PHP mostly uses SQL queries to retrieve data from linked tables using JOIN clauses. To obtain a customer’s name and order information, for example, you would use a database extension such as MySQLi or PDO to JOIN the Customers and Orders tables on the CustomerID column in your PHP code.