为女朋友写一个小程序（二）— —数据库设计

今天药忘吃喽~ 2022-05-03 02:30 270阅读 0赞

[为女朋友写一个小程序（一）— —目的与需求][Link 1]  
为女朋友写一个小程序（二）— —数据库设计(本文)  
[为女朋友写一个小程序（三）— —基于springboot的服务器端接口设计与实现][_springboot]  
[为女朋友写一个小程序（四）— —前端小程序的设计与实现][Link 2]  
[为女朋友写一个小程序（五）— —如何用docker简化部署][_docker]  
[为女朋友写一个小程序（六）— —结合docker实现devOps][_docker_devOps]  
为女朋友写一个小程序（七）— —优化引进redis(未编码，未写)  
为女朋友写一个小程序（八）— —基于moongodb实现即时通讯(未编码，未写)

> 看了一下上一篇文章还是7月份，距离现在已经三个月了，因为工作繁忙连续出差加班两个月少了很多输出的工作（其实是因为懒）。最近连续两个小粉丝发来邮件希望注册软件学习，又再次激起创作兴趣，今晚就把kikiTarget数据库设计给完成了。

## 一、数据库选型 ##

在完成kikiTarget的时候是今年过完年的时候，那时候对NoSQL了解的程序还不深，所以直接使用mysql做数据持久化，其实针对这样的场景其实也是mysql比较适合。  
为什么呢？因为对于这个需求，各种数据结构之间的关联关系是比较强的。先看看ER图  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hheXJl_size_27_color_FFFFFF_t_70]

其中：  
user：存储用户数据。

supervision：存储监督关系，虽然需求设想的是用户与用户之间的监督关系是1:1的关系，但是考虑到如果是1:1的话，需要在user表里面添加一个字段来存储其监督者，感觉耦合太严重，所以设计出了supervision表来描述两个用户之间的监督关系，用户A监督用户B会产生一条监督记录a，用户B监督用户A会产生一条监督记录b。  
这样设计的话，需要让其他记录（任务记录、奖励记录）依赖这条监督关系（相当于同时记录了用户与用户的监督者——连表），然后可以简单通过这条监督关系id去拿到我要的东西。举个例子：

A登录，此时直接获取记录a，b存储到本地，创建一个任务t1，t1里面记录了b。  
B登录，此时直接获取记录a，b存储到本地，此时B想查看其监督的人的任务，通过  
select \* from target where supervision\_id = b  
就能拿到A的任务。

如果使用1:1的关系的话，可以就可以用监督者id去拿，其实都是同样的道理。

target:存储任务数据。

reviewed:存储审核数据。

exchange:存储兑换数据。

prize:存储奖品数据。

store:存储资源数据。

从图中可以看到，这个需求各种数据之间的关联关系是很强的，都是依赖

用户-监督关系
    任务-监督关系
    审核-任务
    兑换-奖品
    奖品-图片资源

所以还是使用SQL类型数据库，才更好的利用外键来强化这种约束关系。

## 二、关于索引设计 ##

因为索引是用于快速查询的，根据该程序的使用范围来看，其实索引的需求并不大，所以并没考虑索引的情况，之前还打算看完高性能Mysql再完成这篇文章，但却因为工作的关系看了Linux的一些书了。

## 三、schema设计 ##

这里直接给出SQL的模型好了，感觉没必要在文档上面浪费太多时间，当初开发的时候我是直接用Hibernate的反向工程，直接设计数据库，自底向上开发的。

-- ----------------------------
    -- Table structure for exchange
    -- ----------------------------
    DROP TABLE IF EXISTS `exchange`;
    CREATE TABLE `exchange` (
      `id` varchar(36) NOT NULL COMMENT 'id',
      `supervision_id` varchar(36) NOT NULL COMMENT '用户id',
      `prize_id` varchar(36) NOT NULL COMMENT '奖品id',
      `create_time` datetime NOT NULL,
      `update_time` datetime NOT NULL,
      `enabled` bit(1) NOT NULL,
      `cost` int(11) NOT NULL COMMENT '支付时所用的积分',
      PRIMARY KEY (`id`),
      KEY `prize_id` (`prize_id`),
      KEY `supervision_id` (`supervision_id`),
      CONSTRAINT `exchange_ibfk_2` FOREIGN KEY (`prize_id`) REFERENCES `prize` (`id`),
      CONSTRAINT `exchange_ibfk_3` FOREIGN KEY (`supervision_id`) REFERENCES `supervision` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Table structure for prize
    -- ----------------------------
    DROP TABLE IF EXISTS `prize`;
    CREATE TABLE `prize` (
      `id` varchar(36) NOT NULL,
      `supervision_id` varchar(36) NOT NULL COMMENT '监督关系id',
      `name` varchar(100) NOT NULL COMMENT '奖品名',
      `store_id` varchar(36) NOT NULL COMMENT '资源地址',
      `worth` decimal(9,2) NOT NULL COMMENT '价值',
      `score` int(11) NOT NULL COMMENT '分值',
      `create_time` datetime NOT NULL,
      `update_time` datetime NOT NULL,
      `enabled` bit(1) NOT NULL COMMENT '默认为1',
      PRIMARY KEY (`id`),
      KEY `resource_id` (`store_id`),
      KEY `prize_ibfk_1` (`supervision_id`),
      CONSTRAINT `prize_ibfk_1` FOREIGN KEY (`supervision_id`) REFERENCES `supervision` (`id`),
      CONSTRAINT `prize_ibfk_2` FOREIGN KEY (`store_id`) REFERENCES `store` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Table structure for reviewed
    -- ----------------------------
    DROP TABLE IF EXISTS `reviewed`;
    CREATE TABLE `reviewed` (
      `id` varchar(36) NOT NULL COMMENT 'uuid',
      `target_id` varchar(36) NOT NULL COMMENT '任务id',
      `state` int(2) NOT NULL COMMENT '状态1：未处理，2：同意，-1：不同意',
      `create_time` datetime NOT NULL COMMENT '创建时间',
      `update_time` datetime NOT NULL COMMENT '修改时间',
      `enabled` bit(1) NOT NULL COMMENT '默认为1',
      `comment` varchar(400) NOT NULL COMMENT '感想',
      `supervision_id` varchar(36) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `target_id` (`target_id`),
      KEY `supervision_id` (`supervision_id`),
      CONSTRAINT `reviewed_ibfk_1` FOREIGN KEY (`target_id`) REFERENCES `target` (`id`),
      CONSTRAINT `reviewed_ibfk_2` FOREIGN KEY (`supervision_id`) REFERENCES `supervision` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Table structure for store
    -- ----------------------------
    DROP TABLE IF EXISTS `store`;
    CREATE TABLE `store` (
      `id` varchar(36) NOT NULL COMMENT 'id',
      `path` varchar(255) NOT NULL COMMENT '资源路径',
      `create_time` datetime NOT NULL,
      `update_time` datetime NOT NULL,
      `enabled` bit(1) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Table structure for supervision
    -- ----------------------------
    DROP TABLE IF EXISTS `supervision`;
    CREATE TABLE `supervision` (
      `id` varchar(36) NOT NULL COMMENT 'uuid',
      `superintendent_id` varchar(36) NOT NULL COMMENT '监督人用户id',
      `user_id` varchar(36) NOT NULL COMMENT '用户id',
      `create_time` datetime NOT NULL,
      `update_time` datetime NOT NULL,
      `enabled` bit(1) NOT NULL DEFAULT b'1' COMMENT '是否启动',
      PRIMARY KEY (`id`),
      KEY `superintendent_id` (`superintendent_id`),
      KEY `user_id` (`user_id`),
      CONSTRAINT `supervision_ibfk_1` FOREIGN KEY (`superintendent_id`) REFERENCES `user` (`id`),
      CONSTRAINT `supervision_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Table structure for target
    -- ----------------------------
    DROP TABLE IF EXISTS `target`;
    CREATE TABLE `target` (
      `id` varchar(36) NOT NULL COMMENT 'id',
      `supervision_id` varchar(36) NOT NULL COMMENT '监督关系id',
      `content` varchar(200) NOT NULL COMMENT '内容',
      `deadline` date NOT NULL COMMENT '截止时间',
      `reward` int(11) NOT NULL COMMENT '奖励积分',
      `punishment` int(11) NOT NULL COMMENT '惩罚分数',
      `state` int(2) NOT NULL COMMENT '状态1：进行中，2：审核中，3：完成，-1：超时',
      `create_time` datetime NOT NULL,
      `update_time` datetime NOT NULL,
      `enabled` bit(1) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `supervision_id` (`supervision_id`),
      CONSTRAINT `target_ibfk_1` FOREIGN KEY (`supervision_id`) REFERENCES `supervision` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Table structure for user
    -- ----------------------------
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user` (
      `id` varchar(36) NOT NULL COMMENT 'id',
      `account` varchar(11) NOT NULL COMMENT '手机号',
      `password` varchar(20) NOT NULL COMMENT '密码',
      `name` varchar(20) NOT NULL COMMENT '姓名',
      `amount` int(11) NOT NULL DEFAULT '0' COMMENT '积分',
      `create_time` datetime NOT NULL COMMENT '创建时间',
      `update_time` datetime NOT NULL COMMENT '修改时间',
      `enabled` bit(1) NOT NULL DEFAULT b'1' COMMENT '是否启动',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果对您有帮助，希望给我一分鼓励~愿你我皆不忘初心！  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hheXJl_size_16_color_FFFFFF_t_70]

[Link 1]: https://blog.csdn.net/hayre/article/details/80146962
[_springboot]: https://blog.csdn.net/hayre/article/details/81041189
[Link 2]: https://blog.csdn.net/hayre/article/details/87135103
[_docker]: https://blog.csdn.net/hayre/article/details/87213508
[_docker_devOps]: https://blog.csdn.net/hayre/article/details/87686845
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hheXJl_size_27_color_FFFFFF_t_70]: /images/20220503/4700afcd0a5646bcb218c6a4a2bd9bcf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hheXJl_size_16_color_FFFFFF_t_70]: /images/20220503/5a63b64db8ca4484b879ad032bf0e694.png