在MySQL中如何建模这个架构

问题描述

希望在MySQL中创建一个能够反映以下关系的架构，他正在尝试在MySQL Workbench中完成这个任务（他并不是数据库专家）。
– 他有三个环境：sandbox（沙盒）、staging（暂存）和production（生产）。
– 他在每个环境中发布一个产品版本，例如，在sandbox中是sb-1.0.0，在staging中是st-1.0.0，在production中是pr-1.0.0。每个环境中可以有多个发布版本，但每个环境只能有一个当前发布版本。
– 每个环境中的每个产品版本包含一组服务，所有环境中的服务集相同，但其版本号不同。例如，sb-1.0.0 = [svc1-2.0.0, svc2-2.0.0]，st-1.0.0 = [svc1-1.0.0, svc2-2.0.0]，pr-1.0.0 = [svc1-1.0.0, svc2-1.0.0]。可以看到，所有环境中都有相同的2个服务，但每个环境中的服务版本可以不同（也可以相同）。

用户希望实现以下多个目标：
1. 在每个环境中创建新的发布版本，每个版本包含一组服务及其对应的版本。例如，他希望能够创建一个版本pr-2.0.0 = [svc1-2.0.0, svc2-1.0.0]。
2. 能够查询数据库，了解特定发布版本（如pr-1.0.0）中的所有服务及其相应版本。
3. 为每个环境中的每个服务定义“当前版本”和“期望版本”的概念。
4. 能够运行脚本（例如Kubernetes脚本），基于查询结果发布每个服务及其相应版本。
5. 能够将系统扩展到更多环境，例如添加一个UAT环境。

用户询问如何建模数据库来实现这些目标。

解决方案

在这种情况下，可以采用以下的数据库模型和设计来实现用户的需求。为了更好地满足用户的目标，可以结合使用表、视图和存储过程。

请注意以下操作可能涉及版本差异，操作前请做好备份。

数据库表设计

为了满足用户的需求，我们可以创建以下几张表来建模数据库：

1. environments 表：存储环境信息。
2. products 表：存储产品版本信息。
3. services 表：存储服务信息。
4. service_versions 表：存储服务版本信息。
5. environment_product_service_mapping 表：建立环境、产品、服务之间的关联。

以下是表结构的示例：

environments 表

Field	Type	Description
id	INT	环境ID
name	VARCHAR(255)	环境名称

products 表

Field	Type	Description
id	INT	产品ID
name	VARCHAR(255)	产品名称
version	VARCHAR(255)	产品版本
environment_id	INT	外键：关联环境

services 表

Field	Type	Description
id	INT	服务ID
name	VARCHAR(255)	服务名称

service_versions 表

Field	Type	Description
id	INT	版本ID
version	VARCHAR(255)	版本号
service_id	INT	外键：关联服务

environment_product_service_mapping 表

Field	Type	Description
id	INT	映射ID
environment_id	INT	外键：关联环境
product_id	INT	外键：关联产品
service_id	INT	外键：关联服务

数据库视图设计

为了方便查询，可以创建一些视图来获取所需的信息。

1. current_versions 视图：显示每个环境中当前发布的版本及其包含的服务和版本。
2. desired_versions 视图：显示每个环境中期望的版本及其包含的服务和版本。

存储过程设计

为了实现创建新版本、查询版本信息等功能，可以编写存储过程来完成这些任务。

示例存储过程：创建新版本

以下是一个示例存储过程，用于在给定环境中创建新版本。

“`sql
DELIMITER //
CREATE PROCEDURE CreateNewVersion(
IN env_name VARCHAR(255),
IN product_name VARCHAR(255),
IN product_version VARCHAR(255),
IN service_versions JSON
)
BEGIN
DECLARE env_id INT;
DECLARE product_id INT;

-- 获取环境ID
SELECT id INTO env_id FROM environments WHERE name = env_name;

-- 获取产品ID
SELECT id INTO product_id FROM products WHERE name = product_name AND version = product_version;

-- 插入新版本信息
INSERT INTO products (name, version, environment_id) VALUES (product_name, product_version, env_id);
SET product_id = LAST_INSERT_ID();

-- 解析服务版本信息并插入
INSERT INTO service_versions (version, service_id)
SELECT JSON_UNQUOTE(JSON_EXTRACT(service_versions, CONCAT('$."', services.name, '"'))) AS version,
       services.id
FROM services
WHERE JSON_UNQUOTE(JSON_EXTRACT(service_versions, CONCAT('$."', services.name, '"'))) IS NOT NULL;