owl-admin/doc/系统设计/数据库设计/物理模型/分析后台设计增加/35通用设置.sql

-- Active: xiaoding_tes@@192.168.110.85@3306@xiaoding_test

/* 删除设置相关表 */
DROP TABLE IF EXISTS `setting_values`;

DROP TABLE IF EXISTS `setting_permissions`;

DROP TABLE IF EXISTS `setting_items`;

DROP TABLE IF EXISTS `setting_groups`;

/* 创建设置组表 */
CREATE TABLE `setting_groups` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT /* 主键ID */,
    `code` varchar(50) NOT NULL /* 设置组编码 */,
    `name` varchar(50) NOT NULL /* 设置组名称 */,
    `description` varchar(255) DEFAULT NULL /* 设置组描述 */,
    `sort` int NOT NULL DEFAULT 0 /* 排序 */,
    `created_at` timestamp NULL DEFAULT NULL /* 创建时间 */,
    `updated_at` timestamp NULL DEFAULT NULL /* 更新时间 */,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_code` (`code`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci /* 设置组表 */;

/* 创建设置项表 */
CREATE TABLE `setting_items` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT /* 主键ID */,
    `group_id` bigint unsigned NOT NULL /* 设置组ID */,
    `code` varchar(50) NOT NULL /* 设置项编码 */,
    `name` varchar(50) NOT NULL /* 设置项名称 */,
    `description` varchar(255) DEFAULT NULL /* 设置项描述 */,
    `value_type` varchar(20) NOT NULL /* 值类型:string,number,boolean,json */,
    `default_value` text DEFAULT NULL /* 默认值 */,
    `min_value` decimal(10, 2) DEFAULT NULL /* 最⼩值(数值类型) */,
    `max_value` decimal(10, 2) DEFAULT NULL /* 最⼤值(数值类型) */,
    `options` json DEFAULT NULL /* 可选值(json格式) */,
    `sort` int NOT NULL DEFAULT 0 /* 排序 */,
    `created_at` timestamp NULL DEFAULT NULL /* 创建时间 */,
    `updated_at` timestamp NULL DEFAULT NULL /* 更新时间 */,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_code` (`code`),
    KEY `idx_group_id` (`group_id`),
    CONSTRAINT `fk_setting_items_group_id` FOREIGN KEY (`group_id`) REFERENCES `setting_groups` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci /* 设置项表 */;

/* 创建设置权限表 */
CREATE TABLE `setting_permissions` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT /* 主键ID */,
    `item_id` bigint unsigned NOT NULL /* 设置项ID */,
    `object_type` varchar(20) NOT NULL /* 业务对象类型:PLATFORM,AGENT,SHOP,COACH */,
    `can_edit` tinyint(1) NOT NULL DEFAULT 0 /* 是否可编辑 */,
    `min_value` decimal(10, 2) DEFAULT NULL /* 最⼩值限制 */,
    `max_value` decimal(10, 2) DEFAULT NULL /* 最⼤值限制 */,
    `options` json DEFAULT NULL /* 可选值限制 */,
    `created_at` timestamp NULL DEFAULT NULL /* 创建时间 */,
    `updated_at` timestamp NULL DEFAULT NULL /* 更新时间 */,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_item_object` (`item_id`, `object_type`),
    KEY `idx_item_id` (`item_id`),
    CONSTRAINT `fk_setting_permissions_item_id` FOREIGN KEY (`item_id`) REFERENCES `setting_items` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci /* 设置权限表 */;

/* 创建设置值表 */
CREATE TABLE `setting_values` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT /* 主键ID */,
    `item_id` bigint unsigned NOT NULL /* 设置项ID */,
    `object_type` varchar(20) NOT NULL /* 业务对象类型:PLATFORM,AGENT,SHOP,COACH */,
    `object_id` bigint unsigned NOT NULL /* 业务对象ID */,
    `value` text NOT NULL /* 设置值 */,
    `created_at` timestamp NULL DEFAULT NULL /* 创建时间 */,
    `updated_at` timestamp NULL DEFAULT NULL /* 更新时间 */,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_item_object` (
        `item_id`,
        `object_type`,
        `object_id`
    ),
    KEY `idx_object` (`object_type`, `object_id`),
    CONSTRAINT `fk_setting_values_item_id` FOREIGN KEY (`item_id`) REFERENCES `setting_items` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci /* 设置值表 */;