论MIS系统权限管理数据库表简要设计
1、创建用户权限表
CREATE TABLE [dbo].[Sys_Privilege](
[PrivilegeID] [char](32) NOT NULL, --主键
[PrivilegeName] [varchar](50) NULL,--权限名称
[PrivilegeCode] [varchar](50) NULL,--权限编码
[ParentPrivilegeID] [char](32) NULL,--上级权限
[ParentPrivilegeName] [varchar](50) NULL,--上级权限名称
[Remark] [varchar](50) NULL,--备注
[Type] [char](1) NULL,--类别
[MenuUrl] [varchar](100) NULL,--权限路径
[MenuPIC] [varchar](100) NULL,--图标
[Displayorder] [numeric](5, 0) NULL,--排序
[UseState] [char](1) NULL,--状态
CONSTRAINT [PK_Sys_Privilege] PRIMARY KEY CLUSTERED
(
[PrivilegeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
2、创建角色表
CREATE TABLE [dbo].[Sys_Role](
[RoleID] [char](32) NOT NULL,--主键
[RoleName] [varchar](20) NULL,--角色名称
[Remark] [char](100) NULL,--备注
[Type] [char](1) NULL,--角色类别
CONSTRAINT [PK_Sys_Role] PRIMARY KEY CLUSTERED
(
[RoleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
3、系统用户表
CREATE TABLE [dbo].[Sys_User](
[UserID] [char](32) NOT NULL,--主键
[TeacherID] [char](32) NOT NULL,--员工ID
[UserName] [varchar](20) NOT NULL,--用户名
[PassWord] [varchar](32) NOT NULL,--密码
[Remark] [varchar](200) NULL,--备强王注
[Lastlogonttime] [datetime] NULL,--最后一次登录泥肤粒时间
[Logonmark] [char](1) NULL,--登录状态
[IP] [varchar](32) NULL,--地址
[IsUSE] [char](1) NULL,--是否启用
罩趴CONSTRAINT [PK_Sys_User] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
4、角色权限表，角色权限表是记录每一个角色具有的角色权限。
CREATE TABLE [dbo].[Sys_RolePrivilege](
[PRIVILEGEID] [char](32) NOT NULL,
[ROLEID] [char](32) NOT NULL
) ON [PRIMARY]
5、用户角色表，用户角色表记录每一个系统用户具有哪些菜单权限。
CREATE TABLE [dbo].[Sys_UserRole](
[UserID] [char](32) NOT NULL,
[RoleID] [char](32) NOT NULL,
CONSTRAINT [PK_SYS_USERROLE] PRIMARY KEY CLUSTERED
(
[UserID] ASC,
[RoleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
6、员工表，最后一表是为了让系统用户和员工区分开来，员工不一定是系统用户，员工也可以转为系统用户。
