Structure design of SQL general permission database table practical lbx

1. User group table

CREATE TABLE [dbo].[rrl_group] (
    [Id] int NOT NULL IDENTITY(1,1) ,
    [name] nvarchar(50) NOT NULL ,
    [status] int NOT NULL ,
    [CreateUserId] int NOT NULL ,
    [CreateUserName] nvarchar(20) NULL ,
    [CreateTime] datetime NOT NULL ,
    [ModifyUserId] int NOT NULL ,
    [ModifyUserName] nvarchar(20) NULL ,
    [ModifyTime] datetime NULL ,
    [code] nvarchar(50) NULL ,
    [type] int NOT NULL DEFAULT ((0)) 
)

2. Authority table

CREATE TABLE [dbo].[rrl_rule] (
    [Id] int NOT NULL IDENTITY(1,1) ,
    [name] nvarchar(100) NOT NULL ,
    [title] nvarchar(100) NULL ,
    [status] int NOT NULL ,
    [CreateUserId] int NOT NULL ,
    [CreateUserName] nvarchar(20) NULL ,
    [CreateTime] datetime NOT NULL ,
    [ModifyUserId] int NOT NULL ,
    [ModifyUserName] nvarchar(20) NULL ,
    [ModifyTime] datetime NULL ,
    [pid] int NOT NULL DEFAULT ((0)) ,
    [url] nvarchar(150) NULL 
)

3. User table

CREATE TABLE [dbo].[rrl_user] (
    [Id] int NOT NULL IDENTITY(1,1) ,
    [username] nvarchar(50) NOT NULL ,
    [password] nvarchar(64) NOT NULL ,
    [name] nvarchar(20) NOT NULL ,
    [phone] nvarchar(20) NULL ,
    [dept] nvarchar(50) NULL ,
    [status] int NOT NULL ,
    [CreateUserId] int NOT NULL ,
    [CreateUserName] nvarchar(20) NULL ,
    [CreateTime] datetime NOT NULL ,
    [ModifyUserId] int NOT NULL ,
    [ModifyUserName] nvarchar(20) NULL ,
    [ModifyTime] datetime NULL 
)

4. User group permission relation table

CREATE TABLE [dbo].[rrl_group_rule] (
    [id] int NOT NULL IDENTITY(1,1) ,
    [rule_id] int NOT NULL ,
    [group_id] int NOT NULL 
)

5. User group user relationship table

CREATE TABLE [dbo].[rrl_group_user] (
    [id] int NOT NULL IDENTITY(1,1) ,
    [user_id] int NOT NULL ,
    [group_id] int NOT NULL  
)

Note: the Id here is in the form of auto increment. For convenience, it is recommended to use GUID, so that the value of Id can be known before inserting data, and it is more convenient to write data. In addition, if the table directly establishes a foreign key relationship, data transmission will be a headache. It is recommended not to use the form of foreign key.

Posted by not_skeletor on Sun, 05 Jan 2020 01:50:27 -0800

Programmer Group