MySQL table design practice: Create a book borrowing record table

MySQL table design practice: Create a book borrowing record table

In the library management system, the borrowing record table is used to record the detailed information of the books borrowed by readers. This article will introduce how to create a book borrowing record table in MySQL, and attach corresponding code examples.

First, we need to determine the fields of the borrowing record table. A basic borrowing record should include the following fields:

1. Record ID (record_id): Each borrowing record should be identified by a unique ID, and an auto-incrementing primary key can be used as the record ID.
2. Book ID (book_id): The borrowed book should have a unique identifier. You can use the ISBN code of the book or other identifiers as the book ID.
3. Reader ID (reader_id): Readers who borrow books should also have a unique identifier. The reader's student ID or other identification can be used as the reader ID.
4. Borrow date (borrow_date): records the date the reader borrowed the book, usually stored in date format.
5. Return date (return_date): records the date when the reader returns the book, usually stored in date format. If the book has not been returned, this field can be set to NULL.
6. Borrowing operator (operator): The operator who borrows books should have a unique identifier. The operator's job number or other identification can be used as the operator ID.

Next, we use the MySQL command to create a table named "borrow_records", whose table structure is as follows:

CREATE TABLE borrow_records (
    record_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT,
    reader_id INT,
    borrow_date DATE,
    return_date DATE,
    operator_id INT
);

In this table, we use an auto-incrementing primary key ( AUTO_INCREMENT) as the record ID to ensure that each record has a unique ID. Book ID, reader ID and operator ID are also all integer types (INT) and can be adjusted according to the actual situation. The borrowing date and return date are stored using date type (DATE) respectively.

Next, we can add some sample data to the borrowing record table for subsequent query and analysis. Use the INSERT INTO command to insert data into the table. The example is as follows:

INSERT INTO borrow_records (book_id, reader_id, borrow_date, return_date, operator_id)
VALUES (1, 1001, '2021-01-01', '2021-01-15', 2001);

INSERT INTO borrow_records (book_id, reader_id, borrow_date, return_date, operator_id)
VALUES (2, 1002, '2021-02-01', '2021-02-15', 2002);

INSERT INTO borrow_records (book_id, reader_id, borrow_date, return_date, operator_id)
VALUES (3, 1003, '2021-03-01', '2021-03-15', 2003);

In this way, we have successfully created a book borrowing record table and added some sample data to the table. Through this table, we can easily query borrowing records, count borrowing status, and track the borrowing and returning status of books.

Summary:
Creating a book borrowing record table in MySQL can help us better manage and track borrowing information. By properly designing the table structure and fields, we can easily add, query and count borrowing records. The creation and use of the book borrowing record table is also an example of database table design, which can help us better understand and apply the principles of database table design.

Note: The above example code is only for convenience of understanding and demonstration. In actual application, it needs to be adjusted and optimized according to specific needs.

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