MySQL table design tutorial: Create a simple book borrowing table

MySQL table design tutorial: Create a simple book borrowing table

Designing tables in the database is an important task in database development. This tutorial will take creating a simple book borrowing table as an example to teach you how to use MySQL for table design.

First, we need to create a new database. In MySQL, you can create a new database with the following command:

CREATE DATABASE library;

Next, we need to select the database we just created:

USE library;

Create a new database named books A table used to store book information. We need to record the following fields for each book: id, title, author, publication_date, status. Use the following command to create this table:

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(100) NOT NULL,
    publication_date DATE,
    status ENUM('available', 'borrowed') DEFAULT 'available'
);

In the above command, we defined an auto-incrementing primary key id as the unique identifier of the book. The title and author fields are used to store the title and author of the book. The publication_date field stores the publication date of the book, and the status field is used to identify the borrowing status of the book. The default is "available".

Next, we create a table named borrowers to store the borrower’s information. Each borrower needs to have a unique id and name. Use the following command to create this table:

CREATE TABLE borrowers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

In order to record the borrowing information of books, we also need to create a table named borrowings. Each piece of borrowing information needs to include the borrower's borrower_id, the book_id of the borrowed book, and the borrowing date borrow_date. Use the following command to create this table:

CREATE TABLE borrowings (
    borrowing_id INT AUTO_INCREMENT PRIMARY KEY,
    borrower_id INT,
    book_id INT,
    borrow_date DATE,
    FOREIGN KEY (borrower_id) REFERENCES borrowers(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);

In the above command, we have used a foreign key association to create the borrowings table with borrowers and booksContact the table to ensure that the borrower and book associated with each borrowing information exist in the corresponding table.

Now, we have successfully created a simple book lending table. You can use the following code to add data to the table:

INSERT INTO books (title, author, publication_date) VALUES 
('Animal Farm', 'George Orwell', '1945-08-17'),
('1984', 'George Orwell', '1949-06-08'),
('To Kill a Mockingbird', 'Harper Lee', '1960-07-11');

INSERT INTO borrowers (name) VALUES 
('John Smith'),
('Jane Doe');

INSERT INTO borrowings (borrower_id, book_id, borrow_date) VALUES 
(1, 1, '2020-01-01'),
(1, 2, '2020-02-01'),
(2, 3, '2020-03-01');

Use the following command to query all data in the book table:

SELECT * FROM books;

Query results:

+----+-----------------------+----------------+-------------------+------------+
| id | title                 | author         | publication_date  | status     |
+----+-----------------------+----------------+-------------------+------------+
|  1 | Animal Farm           | George Orwell  | 1945-08-17        | available  |
|  2 | 1984                  | George Orwell  | 1949-06-08        | available  |
|  3 | To Kill a Mockingbird | Harper Lee     | 1960-07-11        | available  |
+----+-----------------------+----------------+-------------------+------------+

Use the following command to query Query all data in the borrower table:

SELECT * FROM borrowers;

Query results:

+----+-------------+
| id | name        |
+----+-------------+
|  1 | John Smith  |
|  2 | Jane Doe    |
+----+-------------+

Use the following command to query all data in the borrowing information table:

SELECT borrowings.borrowing_id, borrowers.name, books.title, borrowings.borrow_date FROM borrowings 
INNER JOIN borrowers ON borrowers.id = borrowings.borrower_id 
INNER JOIN books ON books.id = borrowings.book_id;

Query results:

+--------------+-------------+-----------------------+-------------+
| borrowing_id | name        | title                 | borrow_date |
+--------------+-------------+-----------------------+-------------+
|            1 | John Smith  | Animal Farm           | 2020-01-01  |
|            2 | John Smith  | 1984                  | 2020-02-01  |
|            3 | Jane Doe    | To Kill a Mockingbird | 2020-03-01  |
+--------------+-------------+-----------------------+-------------+

In this way, we successfully created a simple book borrowing table and performed some basic data queries. Through this example, you can learn how to use MySQL for table design, and master basic table creation and data query skills through code examples. I hope this article will help you master MySQL table design!

The above is the detailed content of MySQL table design tutorial: Create a simple book borrowing table. For more information, please follow other related articles on the PHP Chinese website!