设计一个存放图书的数据库时,我通常会考虑几个主要的实体以及它们之间的关系。以下是一个简单的图书数据库设计的示例:
1. 实体定义
图书 (Books)
BookID (主键, 唯一标识符)
- Title (书名)
- Description (描述或摘要)
- Author (作者)
- ISBN (国际标准书号)
- PublicationDate (出版日期)
- Publisher (出版社)
- Pages (页数)
-
用户 (Users) (可选,如果你希望跟踪借阅信息)
- UserID (主键, 唯一标识符)
- Username (用户名)
- Password (密码, 注意:应使用哈希存储)
- Email (电子邮件)
- PhoneNumber (电话号码)
- ... (其他用户相关信息)
-
借阅记录 (Borrowings) (可选,如果你希望跟踪借阅信息)
- BorrowingID (主键, 唯一标识符)
- BookID (外键, 引用Books表的BookID)
- UserID (外键, 引用Users表的UserID)
- BorrowDate (借阅日期)
- ReturnDate (归还日期, 可为空,直到图书归还)
3. SQL示例
以下是基于上述设计的示例:
CREATE DABATASE BOOK;#创建库 |
USE BOOK;#选择库 |
CREATE TABLE books ( |
CREATE TABLE Users ( `书号` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `书名` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `类别` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `作者` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `出版社` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `单价` float(5, 2) NULL DEFAULT NULL, `数量` int(0) NULL DEFAULT NULL, PRIMARY KEY (`书号`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; |
CREATE TABLE Borrowings ( `读者编号` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `姓名` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `类别号` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `单位` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, `有效性` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`读者编号`) USING BTREE, INDEX `类别号`(`类别号`) USING BTREE, CONSTRAINT `读者表_ibfk_1` FOREIGN KEY (`类别号`) REFERENCES `读者类型表` (`类别号`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; |