产品管理服务--数据库设计

需求分析

用户登录

用户需要手机号注册

管理员用户可以查看所有的用户情况（不能产品等其它内容）

个人用户和公司用户目前没有太大的区别

用户日志

用户的登录信息需要记录

产品

人个用户和公司用户可以创建自己的产品，即公司的实际产品，包括产品的类型、产品英文名称和描述信息。

产品的主题

每个产品都会绑定一系列的主题进行ACL，保证该产品主题不会被其他人访问，确定哪些主题是发布、订阅还是发布/订阅。

设备

每种产品中包含一系列的设备，设备要包含账户和密码，在EMQ X 使用MySQL 登录认证中的mqtt_user是加盐之后的密码，此处要存储一个原始密码。同时要关注设备的在线状态。

设备事件

以下都是设备的事件可以，可以对设备的所有事件存储。

设备的连接时长

用来查看设备的连接信息

应用token

用户用来开发专有的设备端服务器时使用，每一个token对应一个产品。

CREATE USER product_manager@'%' IDENTIFIED  WITH mysql_native_password BY 'product_manager';
-- 创建数据库
create DATABASE IF NOT EXISTS product_manager DEFAULT charset utf8mb4;
-- 授权  开发环境可以添加 alter
GRANT create,insert,select,update,delete,drop ON product_manager.* TO product_manager@'%';

use product_manager;

-- 用户
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户表id',
    `username` varchar(50) COMMENT '用户名',
    `truename` varchar(50) COMMENT '真实姓名',
    `icon` varchar(256) COMMENT '头像',
    `password` varchar(256) NOT NULL COMMENT '用户密码，MD5加密',
    `email` varchar(50) DEFAULT NULL,
    `phone` varchar(20) DEFAULT NULL,
    `role` int(4) NOT NULL COMMENT '角色0-管理员,1-个人用户,2公司用户',
    `user_desc` varchar(256) COMMENT '用户描述',
	`create_time` TIMESTAMP NOT NULL DEFAULT current_timestamp COMMENT '创建时间',
	`update_time` TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp COMMENT '修改时间',
	PRIMARY KEY (`id`),
	UNIQUE KEY `phone_unique` (`phone`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 用户日志
DROP TABLE IF EXISTS `login_logs`;
CREATE TABLE `login_logs` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户日志id',
    `user_id` int(11) NOT NULL COMMENT '用户ID',
    `login_time` TIMESTAMP NOT NULL DEFAULT current_timestamp COMMENT '登录时间',
    `logout_time` TIMESTAMP NOT NULL DEFAULT current_timestamp COMMENT '登出时间',
	`create_time` TIMESTAMP NOT NULL DEFAULT current_timestamp COMMENT '创建时间',
	`update_time` TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp COMMENT '修改时间',
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 产品
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '产品id',
    `user_id` int(11) NOT NULL COMMENT '用户ID',
    `product_name` varchar(256) NOT NULL COMMENT '产品名称',
    `product_en_name` varchar(256) NOT NULL COMMENT '产品的英文名称用来限制topic',
    `product_desc` varchar(512) NOT NULL COMMENT '产品描述',
	`create_time` TIMESTAMP NOT NULL DEFAULT current_timestamp COMMENT '创建时间',
	`update_time` TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp COMMENT '修改时间',
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 产品和主题的绑定
DROP TABLE IF EXISTS `product_topic_bind`;
CREATE TABLE `product_topic_bind` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '产品主题id',
    `product_id` int(11) NOT NULL COMMENT '产品ID',
    `topic_id` int(11) NOT NULL COMMENT '主题ID',
	`create_time` TIMESTAMP NOT NULL DEFAULT current_timestamp COMMENT '创建时间',
	`update_time` TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp COMMENT '修改时间',
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 产品中的设备
DROP TABLE IF EXISTS `device`;
CREATE TABLE `device` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '设备id',
    `product_id` int(11) NOT NULL COMMENT '产品ID',
    `mqtt_user_id` int(11) NOT NULL COMMENT 'mqtt设备名称ID',
    `username` varchar(128) NOT NULL COMMENT '用户名',
  	`password` varchar(128) NOT NULL COMMENT '用户密码,未加密',
    `client_id` varchar(128) NOT NULL COMMENT 'mqtt设备的clientId',
    `connect_status` BOOLEAN NOT NULL COMMENT '连接状态，false，true 连接',
	`create_time` TIMESTAMP NOT NULL DEFAULT current_timestamp COMMENT '创建时间',
	`update_time` TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp COMMENT '修改时间',
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 设备连接信息(连接时长)
DROP TABLE IF EXISTS `connect_logs`;
CREATE TABLE `connect_logs` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '设备连接信息id',
    `device_id` int(11) NOT NULL COMMENT '设备ID',
    `connect_time` TIMESTAMP NOT NULL DEFAULT current_timestamp COMMENT '连接时间',
    `disconnect_time` TIMESTAMP NOT NULL DEFAULT current_timestamp COMMENT '断开连接时间',
	`create_time` TIMESTAMP NOT NULL DEFAULT current_timestamp COMMENT '创建时间',
	`update_time` TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp COMMENT '修改时间',
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 设备事件
DROP TABLE IF EXISTS device_events;
CREATE TABLE `device_events` (
	`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '设备事件id',
    `msg_time` TIMESTAMP NOT NULL DEFAULT current_timestamp COMMENT '消息的时间',
    `product_id` int(11) NOT NULL COMMENT '产品ID',
    `device_id` int(11) NOT NULL COMMENT '设备ID',
    `data_type` TINYINT NOT NULL DEFAULT 1 COMMENT '数据类型:  1:event  2:response',
    `topic` varchar(512) NOT NULL COMMENT '主题',
    `stream_id` varchar(128) NOT NULL COMMENT '数据流ID',
    `data` JSON COMMENT '接收的数据',
    `response_result` JSON COMMENT '返回的数据',
    `create_time` TIMESTAMP NOT NULL DEFAULT current_timestamp COMMENT '创建时间',
	`update_time` TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp COMMENT '修改时间',
	PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 应用token表
DROP TABLE IF EXISTS `application_token`;
CREATE TABLE `application_token` (
	`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '应用token id',
	`user_id` int(11) NOT NULL COMMENT '用户ID',
    `product_id` int(11) NOT NULL COMMENT '产品ID',
    `application_name` varchar(128) NOT NULL COMMENT '应用名称',
    `token` varchar(512) NOT NULL COMMENT 'token',
    `create_time` TIMESTAMP NOT NULL DEFAULT current_timestamp COMMENT '创建时间',
	`update_time` TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp COMMENT '修改时间',
	PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建

MySQL8.0 Docker安装

将上面的sql文件在root下运行即可。

总结

些处的数据表满足没有网关和边缘设备的产品使用，如有兴趣扩展，欢迎一起交流。