物联网应用网站数据库设计

项目实战——物联网应用网站数据库设计

1. 数据表总览

• 用户表（存储用户基本信息）
• Token 表 （存储登录状态，以及请求是否有效）
• 设备表（存储设备基本信息）
• 设备数据表（设备实时发送的数据信息）
• 用户-设备拥有关系表
• 设备历史信息表（包括历史轨迹等）
• 用户反馈表（主题、内容和时间）
• HTTP请求日志（如用户下载数据记录等）

2. 具体设计

• 用户表 user

  create table user
  (
      email varchar(80) not null,
      password varchar(80) not null,
      user_name varchar(80) not null,
      name varchar(80),
      gender int, /* 1 for male, 0 for female */
      work_school varchar(80),
      description text,
      phone varchar(20),
      address varchar(255),
      
      primary key (email)
  );
  

• Token 表 token

  create table token
  (
      email varchar(80) not null,
      token_value varchar(32) not null,
      expire datetime not null,
      
      primary key (email, token_value, expire),
      foreign key (email) references user(email)
  );
  

• 设备表 device

  create table device
  (
      device_id varchar(20) not null,
      class int not null, /* 0 for iot device, 1 for cloud computing device, 2 for database device, 3 for cloud storage device, 4 for satellite device */
      type varchar(255) not null, /* for example, 'ECS', 'GPU Cloud Host', 'Elastic High Energy Calculation' and so on */
      create_time datetime not null,
      device_name varchar(100),
      description text,
      
      primary key (device_id)
  );
  

  type	说明
  0-1	智能家居
  0-2	智能穿戴
  0-3	智能交通
  0-4	智慧城市
  0-5	环境监测
  1-1	弹性云主机ECS
  1-2	GPU云主机
  1-3	弹性高性能计算
  2-1	Oracle MySQL
  2-2	Microsoft SQL Server
  2-3	Redis
  2-4	分布式数据库Memcache
  3-1	对象存储
  3-2	云硬盘
  3-3	云备份
  4-1	通信卫星
  4-2	气象卫星
  4-3	侦察卫星
  4-4	导航卫星

• 设备数据表 device_information

  create table device_information
  (
  	device_id varchar(20) not null,
      device_state boolean not null,
      device_value int not null,
      message text not null,
      longitude float not null,
  	latitude float not null,
      timestamp datetime not null,
      
      primary key (device_id, timestamp),
      foreign key (device_id) references device(device_id)
  );
  

• 用户-设备拥有关系表 own

  create table own
  (
      email varchar(80) not null,
      device_id varchar(20) not null,
      
      primary key (email, device_id),
      foreign key (email) references user(email),
      foreign key (device_id) references device(device_id)
  );
  

• 设备状态信息表 history

  create table history
  (
  	device_id varchar(20) not null,
      device_state boolean not null,
      message text not null,
      longitude float not null,
  	latitude float not null,
      timestamp datetime not null,
      
      primary key (device_id, timestamp),
      foreign key (device_id) references device(device_id)
  );
  

• 用户反馈 reply

  create table reply
  (
  	reply_id int auto_increment not null,
      subject varchar(25),
      content varchar(105),
      submit_time datetime not null,
      
      primary key (reply_id)
  );
  

• HTTP请求日志 log

  create table log
  (
  	log_id int auto_increment not null,
      timestamp datetime not null,
      request_url varchar(100) not null,
      method varchar(4) not null, /* get or post */
      front_data text, /* in json string */
      back_data text, /* in json string */
      operation varchar(100), /* the opeartion of the http request */
      
      primary key (log_id)
  );