基于角色权限管理系统数据库表设计结构(常用电商客户管理后台)

最新推荐文章于 2024-05-25 22:12:54 发布

Rilakkuma

最新推荐文章于 2024-05-25 22:12:54 发布

阅读量7.3k

点赞数 2

文章标签： sql 架构电商

本文链接：https://blog.csdn.net/CRISPY_RICE/article/details/79729995

版权

表结构

用户表

各种角色用户基本信息表：客户、店主、管理员均使用本表；

+---------------------+-------------+------+-----+---------------------+-----------------------------+
| Field               | Type        | Null | Key | Default             | Extra                       |
+---------------------+-------------+------+-----+---------------------+-----------------------------+
| user_id             | int(11)     | NO   | PRI | NULL                | auto_increment              |
| user_account_name   | varchar(32) | NO   |     |                     |                             |
| user_name           | varchar(32) | NO   | MUL |                     |                             |
| user_password       | varchar(32) | NO   |     |                     |                             |
| user_sex            | tinyint(4)  | NO   |     | 0                   |                             |
| user_email          | varchar(64) | NO   |     |                     |                             |
| user_phone_num      | varchar(16) | NO   |     |                     |                             |
| user_telephone      | varchar(16) | NO   |     |                     |                             |
| user_location       | varchar(32) | NO   |     |                     |                             |
| user_detail_address | varchar(64) | NO   |     |                     |                             |
| user_zip_code       | varchar(16) | NO   |     |                     |                             |
| user_identity       | varchar(32) | NO   |     |                     |                             |
| created_at          | timestamp   | NO   |     | 2017-01-01 00:00:00 |                             |
| updated_at          | timestamp   | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+---------------------+-------------+------+-----+---------------------+-----------------------------+

角色类表

定义各种角色：比如客户、店主、管理员；

+----------------+-------------+------+-----+---------------------+-----------------------------+
| Field          | Type        | Null | Key | Default             | Extra                       |
+----------------+-------------+------+-----+---------------------+-----------------------------+
| role_id        | int(11)     | NO   | PRI | NULL                | auto_increment              |
| role_parent_id | int(11)     | NO   |     | 0                   |                             |
| role_name      | varchar(32) | NO   | MUL |                     |                             |
| role_desc      | varchar(64) | NO   |     |                     |                             |
| created_at     | timestamp   | NO   |     | 2017-01-01 00:00:00 |                             |
| updated_at     | timestamp   | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+----------------+-------------+------+-----+---------------------+-----------------------------+

权限表

管理各个权限：查阅、管理角色等等；

+-----------------+-------------+------+-----+---------------------+-----------------------------+
| Field           | Type        | Null | Key | Default             | Extra                       |
+-----------------+-------------+------+-----+---------------------+-----------------------------+
| right_id        | int(11)     | NO   | PRI | NULL                | auto_increment              |
| right_parent_id | int(11)     | NO   |     | 0                   |                             |
| right_name      | varchar(32) | NO   | MUL |                     |                             |
| right_desc      | varchar(64) | NO   |     |                     |                             |
| created_at      | timestamp   | NO   |     | 2017-01-01 00:00:00 |                             |
| updated_at      | timestamp   | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+-----------------+-------------+------+-----+---------------------+-----------------------------+

组表(ds_group)

管理由多个用户组成的组；

+-----------------+-------------+------+-----+---------------------+-----------------------------+
| Field           | Type        | Null | Key | Default             | Extra                       |
+-----------------+-------------+------+-----+---------------------+-----------------------------+
| group_id        | int(11)     | NO   | PRI | NULL                | auto_increment              |
| group_parent_id | int(11)     | NO   |     | 0                   |                             |
| group_name      | varchar(32) | NO   | MUL |                     |                             |
| group_desc      | varchar(64) | NO   |     |                     |                             |
| created_at      | timestamp   | NO   |     | 2017-01-01 00:00:00 |                             |
| updated_at      | timestamp   | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+-----------------+-------------+------+-----+---------------------+-----------------------------+

组-角色表(ds_group_role)

管理组->角色之间的映射关系；

+------------+-----------+------+-----+---------------------+-----------------------------+
| Field      | Type      | Null | Key | Default             | Extra                       |
+------------+-----------+------+-----+---------------------+-----------------------------+
| gr_id      | int(11)   | NO   | PRI | NULL                | auto_increment              |
| group_id   | int(11)   | NO   | MUL | 0                   |                             |
| role_id    | int(11)   | NO   | MUL | 0                   |                             |
| created_at | timestamp | NO   |     | 2017-01-01 00:00:00 |                             |
| updated_at | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+------------+-----------+------+-----+---------------------+-----------------------------+

组-权限表(ds_group_right)

管理组->权限之间的映射关系；

+------------+------------+------+-----+---------------------+-----------------------------+
| Field      | Type       | Null | Key | Default             | Extra                       |
+------------+------------+------+-----+---------------------+-----------------------------+
| gri_id     | int(11)    | NO   | PRI | NULL                | auto_increment              |
| group_id   | int(11)    | NO   | MUL | 0                   |                             |
| right_id   | int(11)    | NO   | MUL | 0                   |                             |
| right_type | tinyint(4) | NO   |     | 0                   |                             |
| created_at | timestamp  | NO   |     | 2017-01-01 00:00:00 |                             |
| updated_at | timestamp  | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+------------+------------+------+-----+---------------------+-----------------------------+

用户-角色表(ds_user_role)

管理用户->角色之间的映射关系；

+------------+-----------+------+-----+---------------------+-----------------------------+
| Field      | Type      | Null | Key | Default             | Extra                       |
+------------+-----------+------+-----+---------------------+-----------------------------+
| ur_id      | int(11)   | NO   | PRI | NULL                | auto_increment              |
| user_id    | int(11)   | NO   | MUL | 0                   |                             |
| role_id    | int(11)   | NO   | MUL | 0                   |                             |
| created_at | timestamp | NO   |     | 2017-01-01 00:00:00 |                             |
| updated_at | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+------------+-----------+------+-----+---------------------+-----------------------------+

用户-权限表(ds_user_right)

管理用户->权限之间的映射关系；

+------------+------------+------+-----+---------------------+-----------------------------+
| Field      | Type       | Null | Key | Default             | Extra                       |
+------------+------------+------+-----+---------------------+-----------------------------+
| ur_id      | int(11)    | NO   | PRI | NULL                | auto_increment              |
| user_id    | int(11)    | NO   | MUL | 0                   |                             |
| right_id   | int(11)    | NO   | MUL | 0                   |                             |
| right_type | tinyint(4) | NO   |     | 0                   |                             |
| created_at | timestamp  | NO   |     | 2017-01-01 00:00:00 |                             |
| updated_at | timestamp  | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+------------+------------+------+-----+---------------------+-----------------------------+

用户-组表(ds_user_group)

管理用户->组之间的映射关系；

+------------+-----------+------+-----+---------------------+-----------------------------+
| Field      | Type      | Null | Key | Default             | Extra                       |
+------------+-----------+------+-----+---------------------+-----------------------------+
| ug_id      | int(11)   | NO   | PRI | NULL                | auto_increment              |
| user_id    | int(11)   | NO   | MUL | 0                   |                             |
| group_id   | int(11)   | NO   | MUL | 0                   |                             |
| created_at | timestamp | NO   |     | 2017-01-01 00:00:00 |                             |
| updated_at | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+------------+-----------+------+-----+---------------------+-----------------------------+

店铺表(ds_store)

店长店铺类；

+------------------+--------------+------+-----+---------------------+-----------------------------+
| Field            | Type         | Null | Key | Default             | Extra                       |
+------------------+--------------+------+-----+---------------------+-----------------------------+
| store_id         | int(11)      | NO   | PRI | NULL                | auto_increment              |
| store_manager_id | int(11)      | NO   | MUL | 0                   |                             |
| store_phone_num  | varchar(16)  | NO   |     |                     |                             |
| store_telephone  | varchar(16)  | NO   |     |                     |                             |
| store_name       | varchar(64)  | NO   |     |                     |                             |
| store_posistion  | varchar(128) | NO   |     |                     |                             |
| created_at       | timestamp    | NO   |     | 2017-01-01 00:00:00 |                             |
| updated_at       | timestamp    | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+------------------+--------------+------+-----+---------------------+-----------------------------+

商品表(ds_product)

和也出售商品类；

+--------------+-------------+------+-----+---------------------+-----------------------------+
| Field        | Type        | Null | Key | Default             | Extra                       |
+--------------+-------------+------+-----+---------------------+-----------------------------+
| pro_id       | int(11)     | NO   | PRI | NULL                | auto_increment              |
| store_id     | int(11)     | NO   | MUL | 0                   |                             |
| pro_name     | varchar(64) | NO   | MUL |                     |                             |
| pro_model    | varchar(64) | NO   |     |                     |                             |
| pro_price    | bigint(20)  | NO   |     | 0                   |                             |
| pro_bar_code | bigint(20)  | NO   |     | 0                   |                             |
| created_at   | timestamp   | NO   |     | 2017-01-01 00:00:00 |                             |
| updated_at   | timestamp   | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+--------------+-------------+------+-----+---------------------+-----------------------------+

消费表(ds_consume)

消费记录表；

+------------+------------+------+-----+---------------------+-----------------------------+
| Field      | Type       | Null | Key | Default             | Extra                       |
+------------+------------+------+-----+---------------------+-----------------------------+
| csm_id     | int(11)    | NO   | PRI | NULL                | auto_increment              |
| ctm_id     | int(11)    | NO   | MUL | 0                   |                             |
| pro_id     | int(11)    | NO   | MUL | 0                   |                             |
| csm_money  | bigint(20) | NO   |     | 0                   |                             |
| csm_at     | timestamp  | NO   |     | 2017-01-01 00:00:00 |                             |
| updated_at | timestamp  | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+------------+------------+------+-----+---------------------+-----------------------------+

SQL初始化语句

-- drop database heye_crm;
CREATE DATABASE IF NOT EXISTS heye_crm;
use `heye_crm`;

CREATE TABLE IF NOT EXISTS `ds_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_account_name` varchar(32) NOT NULL DEFAULT '',
  `user_name` varchar(32) NOT NULL DEFAULT '',
  `user_password` varchar(32) NOT NULL DEFAULT '',
  `user_sex` tinyint(4) NOT NULL DEFAULT '0',
  `user_email` varchar(64) NOT NULL DEFAULT '',
  `user_phone_num` varchar(16) NOT NULL DEFAULT '',
  `user_telephone` varchar(16) NOT NULL DEFAULT '',
  `user_location` varchar(32) NOT NULL DEFAULT '',
  `user_detail_address` varchar(64) NOT NULL DEFAULT '',
  `user_zip_code` varchar(16) NOT NULL DEFAULT '',
  `user_identity` varchar(32) NOT NULL DEFAULT '', 
  `created_at` timestamp NOT NULL DEFAULT "2017-01-01",
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  KEY `user_name` (`user_name`,`user_password`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ds_role` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `role_parent_id` int(11) NOT NULL DEFAULT 0,
  `role_name` varchar(32) NOT NULL DEFAULT '',
  `role_desc` varchar(64) NOT NULL DEFAULT '',
  `created_at` timestamp NOT NULL DEFAULT "2017-01-01",
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`role_id`),
  KEY `role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ds_right` (
  `right_id` int(11) NOT NULL AUTO_INCREMENT,
  `right_parent_id` int(11) NOT NULL DEFAULT 0,
  `right_name` varchar(32) NOT NULL DEFAULT '',
  `right_desc` varchar(64) NOT NULL DEFAULT '',
  `created_at` timestamp NOT NULL DEFAULT "2017-01-01",
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`right_id`),
  KEY `right_name` (`right_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ds_group` (
  `group_id` int(11) NOT NULL AUTO_INCREMENT,
  `group_parent_id` int(11) NOT NULL DEFAULT 0,
  `group_name` varchar(32) NOT NULL DEFAULT '',
  `group_desc` varchar(64) NOT NULL DEFAULT '',
  `created_at` timestamp NOT NULL DEFAULT "2017-01-01",
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`group_id`),
  KEY `group_name` (`group_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `ds_group_role` (
  `gr_id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL DEFAULT 0,
  `role_id` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT "2017-01-01",
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`gr_id`),
  KEY `group_id` (`group_id`),
  FOREIGN KEY(`group_id`) REFERENCES ds_group(`group_id`),
  FOREIGN KEY(`role_id`) REFERENCES ds_role(`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `ds_group_right` (
  `gri_id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL DEFAULT 0,
  `right_id` int(11) NOT NULL DEFAULT 0,
  `right_type` tinyint(4) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT "2017-01-01",
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`gri_id`),
  KEY `group_id` (`group_id`),
  KEY `group_right` (`group_id`, `right_id`),
  FOREIGN KEY(`group_id`) REFERENCES ds_group(`group_id`),
  FOREIGN KEY(`right_id`) REFERENCES ds_right(`right_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `ds_user_role` (
  `ur_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT 0,
  `role_id` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT "2017-01-01",
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ur_id`),
  KEY `user_id` (`user_id`),
  KEY `user_role` (`user_id`, `role_id`),
  FOREIGN KEY(`user_id`) REFERENCES ds_user(`user_id`),
  FOREIGN KEY(`role_id`) REFERENCES ds_role(`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ds_user_right` (
  `ur_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT 0,
  `right_id` int(11) NOT NULL DEFAULT 0,
  `right_type` tinyint(4) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT "2017-01-01",
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ur_id`),
  KEY `user_id` (`user_id`),
  KEY `user_right` (`user_id`,`right_id`),
  FOREIGN KEY(`user_id`) REFERENCES ds_user(`user_id`),
  FOREIGN KEY(`right_id`) REFERENCES ds_right(`right_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ds_user_group` (
  `ug_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT 0,
  `group_id` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT "2017-01-01",
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ug_id`),
  KEY `user_id` (`user_id`),
  KEY `group_id` (`group_id`),
  FOREIGN KEY(`user_id`) REFERENCES ds_user(`user_id`),
  FOREIGN KEY(`group_id`) REFERENCES ds_group(`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ds_store` (
  `store_id` int(11) NOT NULL AUTO_INCREMENT,
  `store_manager_id` int(11) NOT NULL DEFAULT 0,
  `store_phone_num` varchar(16) NOT NULL DEFAULT '',
  `store_telephone` varchar(16) NOT NULL DEFAULT '',
  `store_name` varchar(64) NOT NULL DEFAULT '',
  `store_posistion` varchar(128) NOT NULL DEFAULT '',
  `created_at` timestamp NOT NULL DEFAULT "2017-01-01",
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`store_id`),
  KEY `store_manager_id` (`store_manager_id`),
  FOREIGN KEY(`store_manager_id`) REFERENCES ds_user(`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ds_product` (
  `pro_id` int(11) NOT NULL AUTO_INCREMENT,
  `store_id` int(11) NOT NULL DEFAULT 0,
  `pro_name` varchar(64) NOT NULL DEFAULT '',
  `pro_model` varchar(64) NOT NULL DEFAULT '',
  `pro_price` bigint(20) NOT NULL DEFAULT 0,
  `pro_bar_code` bigint(20) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT "2017-01-01",
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`pro_id`),
  KEY `store_id` (`store_id`),
  KEY `pro_name` (`pro_name`),
  FOREIGN KEY(`store_id`) REFERENCES ds_store(`store_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ds_consume` (
  `csm_id` int(11) NOT NULL AUTO_INCREMENT,
  `ctm_id` int(11) NOT NULL DEFAULT 0,
  `pro_id` int(11) NOT NULL DEFAULT 0,
  `csm_money` bigint(20) NOT NULL DEFAULT 0,
  `csm_at` timestamp NOT NULL DEFAULT "2017-01-01",
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`csm_id`),
  KEY `ctm_id` (`ctm_id`),
  FOREIGN KEY(`ctm_id`) REFERENCES ds_user(`user_id`),
  FOREIGN KEY(`pro_id`) REFERENCES ds_product(`pro_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---
insert into ds_role(role_name, role_desc) values('test', 'test');
insert into ds_group(group_name, group_desc) values('test', 'test');
insert into ds_group_role(group_id, role_id) values(1, 1);

Rilakkuma

关注

2
点赞
踩
33

收藏

觉得还不错? 一键收藏
0
评论
基于角色权限管理系统数据库表设计结构(常用电商客户管理后台)

表结构用户表各种角色用户基本信息表：客户、店主、管理员均使用本表； +---------------------+-------------+------+-----+---------------------+-----------------------------+| Field | Type | Null | Key...
复制链接

扫一扫