sql: Data Modeling Patterns using sql server

 

/* =====================================================================
   珠宝行业数据建模模式完整脚本
   数据库: SQL Server 2019+
   业务场景: 珠宝原料 -> 款式设计 -> 加工工单 -> 库存 -> 经销商 -> 销售
   覆盖: 1NF~5NF / 反范式 / Lookup表 / Junction表 / SCD / 层次模型 / 多租户
   执行方式: 在 SSMS 中按 F5 整段执行，或用 sqlcmd -i 本文件
   ===================================================================== */

SET NOCOUNT ON;
GO

/* =====================================================================
   0. 准备：清理并创建数据库
   ===================================================================== */
USE master;
GO
IF DB_ID('JewelryERP') IS NOT NULL
BEGIN
    ALTER DATABASE JewelryERP SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE JewelryERP;
END
GO
CREATE DATABASE JewelryERP;
GO
USE JewelryERP;
GO
PRINT '=== 数据库 JewelryERP 已创建 ===';
GO

/* =====================================================================
   一、范式 Normalization (1NF ~ 5NF)
   ===================================================================== */
PRINT '=== 一、范式示例 ===';
GO

/* ---------- 1NF: 列原子性，无重复组 ---------- */
-- 反例（不执行，仅展示）: GemMaterials 字段存 '红宝石,钻石' 多值
-- 正例: 每个宝石一行
CREATE TABLE dbo.Style_Gem_1NF
(
    Id         INT IDENTITY(1,1) PRIMARY KEY,
    StyleId    INT NOT NULL,
    StyleName  NVARCHAR(100) NOT NULL,
    GemName    NVARCHAR(50)  NOT NULL
);
GO
INSERT INTO dbo.Style_Gem_1NF (StyleId, StyleName, GemName)
VALUES (1, N'铂金戒指', N'红宝石'),
       (1, N'铂金戒指', N'钻石'),
       (2, N'黄金吊坠', N'蓝宝石');
GO
PRINT '1NF 表 Style_Gem_1NF 已创建并插入 3 行';
GO

/* ---------- 2NF: 消除部分函数依赖 ---------- */
-- 复合主键 (StyleId, GemId) 时，非键字段必须完全依赖整个主键
-- 先建主表，再建关联表（修正原脚本外键顺序）
CREATE TABLE dbo.Style_2NF
(
    StyleId    INT PRIMARY KEY,
    StyleName  NVARCHAR(100) NOT NULL,
    StyleCode  NVARCHAR(50)  NOT NULL
);
GO
CREATE TABLE dbo.Gem_2NF
(
    GemId      INT PRIMARY KEY,
    GemName    NVARCHAR(50) NOT NULL
);
GO
CREATE TABLE dbo.Style_Gem_Map_2NF
(
    StyleId    INT NOT NULL,
    GemId      INT NOT NULL,
    Carat      DECIMAL(10,3) NOT NULL,
    PRIMARY KEY (StyleId, GemId),
    FOREIGN KEY (StyleId) REFERENCES dbo.Style_2NF(StyleId),
    FOREIGN KEY (GemId)   REFERENCES dbo.Gem_2NF(GemId)
);
GO
INSERT INTO dbo.Style_2NF (StyleId, StyleName, StyleCode)
VALUES (1, N'铂金戒指', 'ST-001'), (2, N'黄金吊坠', 'ST-002');
INSERT INTO dbo.Gem_2NF (GemId, GemName)
VALUES (1, N'红宝石'), (2, N'钻石'), (3, N'蓝宝石');
INSERT INTO dbo.Style_Gem_Map_2NF (StyleId, GemId, Carat)
VALUES (1,1,0.5), (1,2,0.3), (2,3,0.8);
GO
PRINT '2NF 表 Style_2NF / Gem_2NF / Style_Gem_Map_2NF 已创建';
GO

/* ---------- 3NF: 消除传递依赖 + Lookup Table ---------- */
-- GemTypeName 依赖 GemTypeCode（非主键），需拆出查找表
CREATE TABLE dbo.GemType_Lookup_3NF
(
    GemTypeCode  NVARCHAR(20) PRIMARY KEY,
    GemTypeName  NVARCHAR(50) NOT NULL,
    Remark       NVARCHAR(200) NULL
);
GO
CREATE TABLE dbo.GemRaw_3NF
(
    GemId        INT PRIMARY KEY,
    GemCode      NVARCHAR(50) NOT NULL,
    GemTypeCode  NVARCHAR(20) NOT NULL,
    WeightCarat  DECIMAL(10,3) NOT NULL,
    FOREIGN KEY (GemTypeCode) REFERENCES dbo.GemType_Lookup_3NF(GemTypeCode)
);
GO
INSERT INTO dbo.GemType_Lookup_3NF (GemTypeCode, GemTypeName, Remark)
VALUES ('D', N'钻石', N'最硬宝石'),
       ('R', N'红宝石', N'刚玉族'),
       ('S', N'蓝宝石', N'刚玉族');
INSERT INTO dbo.GemRaw_3NF (GemId, GemCode, GemTypeCode, WeightCarat)
VALUES (1, 'GEM-001', 'D', 1.0),
       (2, 'GEM-002', 'R', 0.8),
       (3, 'GEM-003', 'S', 1.2);
GO
PRINT '3NF + Lookup 表 GemType_Lookup_3NF / GemRaw_3NF 已创建';
GO

/* ---------- BCNF: 每个决定因子都必须是候选键 ---------- */
-- 业务规则: 一个工序固定由一位工艺师负责 -> ProcessCode 决定 CraftsmanId
-- 若主键是 (ProcessCode, CraftsmanId)，则 ProcessCode 是决定因子但不是候选键，违反BCNF
-- 修正: ProcessCode 单独作主键，CraftsmanId 为普通属性
CREATE TABLE dbo.Process_BCNF
(
    ProcessCode   NVARCHAR(20) PRIMARY KEY,
    ProcessName   NVARCHAR(100) NOT NULL,
    CraftsmanId   INT NOT NULL
);
GO
CREATE TABLE dbo.Process_Workshop_BCNF
(
    ProcessCode   NVARCHAR(20),
    WorkshopId    INT,
    PRIMARY KEY (ProcessCode, WorkshopId),
    FOREIGN KEY (ProcessCode) REFERENCES dbo.Process_BCNF(ProcessCode)
);
GO
INSERT INTO dbo.Process_BCNF (ProcessCode, ProcessName, CraftsmanId)
VALUES ('P01', N'镶嵌', 101), ('P02', N'抛光', 102), ('P03', N'铸造', 103);
INSERT INTO dbo.Process_Workshop_BCNF (ProcessCode, WorkshopId)
VALUES ('P01',1), ('P01',2), ('P02',1), ('P03',3);
GO
PRINT 'BCNF 表 Process_BCNF / Process_Workshop_BCNF 已创建';
GO

/* ---------- 4NF: 消除多值依赖 + Junction Table ---------- */
-- 款式可以有多个宝石、多个工艺；宝石和工艺互相独立，不能放一张表
-- 拆成两张独立关联表（多对多中间表）
CREATE TABLE dbo.Style_Gem_Junction_4NF
(
    StyleId  INT,
    GemId    INT,
    PRIMARY KEY (StyleId, GemId),
    FOREIGN KEY (StyleId) REFERENCES dbo.Style_2NF(StyleId),
    FOREIGN KEY (GemId)   REFERENCES dbo.Gem_2NF(GemId)
);
GO
CREATE TABLE dbo.Style_Process_Junction_4NF
(
    StyleId     INT,
    ProcessCode NVARCHAR(20),
    PRIMARY KEY (StyleId, ProcessCode),
    FOREIGN KEY (StyleId)     REFERENCES dbo.Style_2NF(StyleId),
    FOREIGN KEY (ProcessCode) REFERENCES dbo.Process_BCNF(ProcessCode)
);
GO
INSERT INTO dbo.Style_Gem_Junction_4NF (StyleId, GemId)
VALUES (1,1),(1,2),(2,3);
INSERT INTO dbo.Style_Process_Junction_4NF (StyleId, ProcessCode)
VALUES (1,'P01'),(1,'P02'),(2,'P01'),(2,'P03');
GO
PRINT '4NF + Junction 表 Style_Gem_Junction_4NF / Style_Process_Junction_4NF 已创建';
GO

/* ---------- 5NF: 投影-连接范式，三方关系不可无损拆分 ---------- */
-- 经销商-款式-材质 三方授权: 经销商可销售某款式，仅在指定材质下生效
-- 不能拆成任意两两表还能无损还原
CREATE TABLE dbo.Material_Lookup
(
    MaterialId    INT PRIMARY KEY,
    MaterialName  NVARCHAR(50) NOT NULL
);
GO
CREATE TABLE dbo.Dealer_5NF
(
    DealerId    INT PRIMARY KEY,
    DealerName  NVARCHAR(100) NOT NULL
);
GO
CREATE TABLE dbo.Dealer_Style_Material_5NF
(
    DealerId      INT NOT NULL,
    StyleId       INT NOT NULL,
    MaterialId    INT NOT NULL,
    IsAuthorized  BIT NOT NULL DEFAULT 0,
    PRIMARY KEY (DealerId, StyleId, MaterialId),
    FOREIGN KEY (DealerId)   REFERENCES dbo.Dealer_5NF(DealerId),
    FOREIGN KEY (StyleId)    REFERENCES dbo.Style_2NF(StyleId),
    FOREIGN KEY (MaterialId) REFERENCES dbo.Material_Lookup(MaterialId)
);
GO
INSERT INTO dbo.Material_Lookup (MaterialId, MaterialName)
VALUES (1, N'18K白金'), (2, N'18K黄金'), (3, N'铂金PT950');
INSERT INTO dbo.Dealer_5NF (DealerId, DealerName)
VALUES (1, N'深圳水贝经销商A'), (2, N'广州荔湾经销商B');
INSERT INTO dbo.Dealer_Style_Material_5NF (DealerId, StyleId, MaterialId, IsAuthorized)
VALUES (1,1,1,1),(1,1,3,1),(1,2,2,1),(2,1,1,1);
GO
PRINT '5NF 三方关系表 Dealer_Style_Material_5NF 已创建';
GO

/* =====================================================================
   二、反范式 Denormalization + 触发器同步
   ===================================================================== */
PRINT '=== 二、反范式示例 ===';
GO
CREATE TABLE dbo.Inventory_Denormalized
(
    InventoryId  INT PRIMARY KEY IDENTITY(1,1),
    StyleId      INT NOT NULL,
    StyleName    NVARCHAR(100) NOT NULL,  -- 冗余字段
    GemId        INT NOT NULL,
    GemName      NVARCHAR(50) NOT NULL,    -- 冗余字段
    StockQty     INT NOT NULL,
    UnitCost     DECIMAL(18,2) NOT NULL,
    UpdateTime   DATETIME DEFAULT GETDATE()
);
GO
INSERT INTO dbo.Inventory_Denormalized (StyleId, StyleName, GemId, GemName, StockQty, UnitCost)
VALUES (1, N'铂金戒指', 1, N'红宝石', 50, 8800.00),
       (1, N'铂金戒指', 2, N'钻石',   30, 12000.00),
       (2, N'黄金吊坠', 3, N'蓝宝石', 80, 3500.00);
GO
-- 触发器: 主表款式名变更时同步冗余字段
CREATE TRIGGER dbo.trg_Style_Update_Denorm
ON dbo.Style_2NF
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF UPDATE(StyleName)
    BEGIN
        UPDATE inv
        SET inv.StyleName = inserted.StyleName,
            inv.UpdateTime = GETDATE()
        FROM dbo.Inventory_Denormalized inv
        INNER JOIN inserted ON inv.StyleId = inserted.StyleId;
    END
END
GO
-- 验证触发器: 修改款式名
UPDATE dbo.Style_2NF SET StyleName = N'铂金六爪戒指' WHERE StyleId = 1;
GO
PRINT '反范式表 Inventory_Denormalized 已创建，触发器 trg_Style_Update_Denorm 已生效';
PRINT '已验证: StyleId=1 款式名变更后，冗余字段自动同步';
GO

/* =====================================================================
   三、Slowly Changing Dimensions (SCD Type 1/2/3)
   维度: 珠宝经销商
   ===================================================================== */
PRINT '=== 三、SCD 缓慢变化维度 ===';
GO

/* ---------- SCD Type 1: 直接覆盖（丢失历史） ---------- */
CREATE TABLE dbo.Dim_Dealer_SCD1
(
    DealerKey   INT PRIMARY KEY IDENTITY(1,1),
    DealerCode  NVARCHAR(30) NOT NULL UNIQUE,
    DealerName  NVARCHAR(100) NOT NULL,
    Address     NVARCHAR(200) NOT NULL
);
GO
INSERT INTO dbo.Dim_Dealer_SCD1 (DealerCode, DealerName, Address)
VALUES ('D001', N'深圳珠宝经销商A', N'罗湖区水贝');
GO
-- Type1 更新: 直接覆盖
UPDATE dbo.Dim_Dealer_SCD1 SET Address = N'龙岗区布吉' WHERE DealerCode = 'D001';
GO
PRINT 'SCD Type1 表 Dim_Dealer_SCD1 已创建（直接覆盖，旧地址丢失）';
GO

/* ---------- SCD Type 2: 新增行 + 生效/失效日期（最常用，保留完整历史） ---------- */
CREATE TABLE dbo.Dim_Dealer_SCD2
(
    DealerSurrogateKey  INT PRIMARY KEY IDENTITY(1,1),
    DealerCode          NVARCHAR(30) NOT NULL,
    DealerName          NVARCHAR(100) NOT NULL,
    Address             NVARCHAR(200) NOT NULL,
    StartDate           DATETIME NOT NULL,
    EndDate             DATETIME NULL,   -- NULL = 当前有效
    IsCurrent           BIT NOT NULL DEFAULT 1
);
GO
-- 初始版本
INSERT INTO dbo.Dim_Dealer_SCD2 (DealerCode, DealerName, Address, StartDate, EndDate, IsCurrent)
VALUES ('D001', N'深圳珠宝经销商A', N'罗湖区水贝', '2024-01-01', NULL, 1);
GO
-- Type2 更新: 旧记录失效 + 新增版本
BEGIN TRANSACTION;
UPDATE dbo.Dim_Dealer_SCD2
SET EndDate = '2025-06-01', IsCurrent = 0
WHERE DealerCode = 'D001' AND IsCurrent = 1;

INSERT INTO dbo.Dim_Dealer_SCD2 (DealerCode, DealerName, Address, StartDate, EndDate, IsCurrent)
VALUES ('D001', N'深圳珠宝经销商A', N'龙岗区布吉', '2025-06-01', NULL, 1);
COMMIT;
GO
-- 再做一次变更，演示3个版本
BEGIN TRANSACTION;
UPDATE dbo.Dim_Dealer_SCD2
SET EndDate = '2026-01-01', IsCurrent = 0
WHERE DealerCode = 'D001' AND IsCurrent = 1;

INSERT INTO dbo.Dim_Dealer_SCD2 (DealerCode, DealerName, Address, StartDate, EndDate, IsCurrent)
VALUES ('D001', N'深圳珠宝经销商A（旗舰店）', N'福田区CBD', '2026-01-01', NULL, 1);
COMMIT;
GO
PRINT 'SCD Type2 表 Dim_Dealer_SCD2 已创建，包含 3 个历史版本';
GO

/* ---------- SCD Type 3: 新增字段保存上一次值（只保留最近一次历史） ---------- */
CREATE TABLE dbo.Dim_Dealer_SCD3
(
    DealerKey           INT PRIMARY KEY IDENTITY(1,1),
    DealerCode          NVARCHAR(30) NOT NULL UNIQUE,
    DealerName          NVARCHAR(100) NOT NULL,
    CurrentAddress      NVARCHAR(200) NOT NULL,
    PreviousAddress     NVARCHAR(200) NULL,
    AddressChangeDate   DATETIME NULL
);
GO
INSERT INTO dbo.Dim_Dealer_SCD3 (DealerCode, DealerName, CurrentAddress, PreviousAddress, AddressChangeDate)
VALUES ('D001', N'深圳珠宝经销商A', N'龙岗区布吉', N'罗湖区水贝', '2025-06-01');
GO
PRINT 'SCD Type3 表 Dim_Dealer_SCD3 已创建（仅保留上一次地址）';
GO

/* =====================================================================
   四、层次模型 Hierarchical Models
   业务: 珠宝产品分类树
   首饰 > 戒指 > 钻戒 > 18K白金钻戒
   ===================================================================== */
PRINT '=== 四、层次模型 ===';
GO

/* ---------- 4.1 Adjacency List 邻接表（最常用） ---------- */
CREATE TABLE dbo.ProductCategory_AdjacencyList
(
    CategoryId    INT PRIMARY KEY IDENTITY(1,1),
    CategoryName  NVARCHAR(100) NOT NULL,
    ParentId      INT NULL,
    SortOrder     INT NOT NULL DEFAULT 0,
    FOREIGN KEY (ParentId) REFERENCES dbo.ProductCategory_AdjacencyList(CategoryId)
);
GO
SET IDENTITY_INSERT dbo.ProductCategory_AdjacencyList ON;
INSERT INTO dbo.ProductCategory_AdjacencyList (CategoryId, CategoryName, ParentId, SortOrder)
VALUES
(1,  N'首饰',       NULL, 1),
(2,  N'戒指',       1,    1),
(3,  N'吊坠',       1,    2),
(4,  N'手链',       1,    3),
(5,  N'耳饰',       1,    4),
(6,  N'钻戒',       2,    1),
(7,  N'素圈戒指',   2,    2),
(8,  N'宝石戒指',   2,    3),
(9,  N'18K白金钻戒',6,    1),
(10, N'铂金钻戒',   6,    2),
(11, N'钻石吊坠',   3,    1),
(12, N'宝石吊坠',   3,    2),
(13, N'18K金手链',  4,    1),
(14, N'钻石耳钉',   5,    1);
SET IDENTITY_INSERT dbo.ProductCategory_AdjacencyList OFF;
GO
PRINT '邻接表 ProductCategory_AdjacencyList 已创建，14 个节点';
GO

/* ---------- 4.2 Nested Set 嵌套集（左右值模型） ---------- */
-- 对应上面同一棵树的左右值
CREATE TABLE dbo.ProductCategory_NestedSet
(
    CategoryId    INT PRIMARY KEY,
    CategoryName  NVARCHAR(100) NOT NULL,
    LeftValue     INT NOT NULL,
    RightValue    INT NOT NULL
);
GO
INSERT INTO dbo.ProductCategory_NestedSet (CategoryId, CategoryName, LeftValue, RightValue)
VALUES
(1,  N'首饰',       1,  28),
(2,  N'戒指',       2,  13),
(3,  N'吊坠',       14, 19),
(4,  N'手链',       20, 23),
(5,  N'耳饰',       24, 27),
(6,  N'钻戒',       3,  8),
(7,  N'素圈戒指',   9,  10),
(8,  N'宝石戒指',   11, 12),
(9,  N'18K白金钻戒',4,  5),
(10, N'铂金钻戒',   6,  7),
(11, N'钻石吊坠',   15, 16),
(12, N'宝石吊坠',   17, 18),
(13, N'18K金手链',  21, 22),
(14, N'钻石耳钉',   25, 26);
GO
PRINT '嵌套集表 ProductCategory_NestedSet 已创建，14 个节点';
GO

/* ---------- 4.3 Closure Table 闭包表 ---------- */
CREATE TABLE dbo.ProductCategory_Closure_Main
(
    CategoryId    INT PRIMARY KEY,
    CategoryName  NVARCHAR(100) NOT NULL
);
GO
CREATE TABLE dbo.ProductCategory_Closure_Relation
(
    AncestorId    INT NOT NULL,
    DescendantId  INT NOT NULL,
    Depth         INT NOT NULL,
    PRIMARY KEY (AncestorId, DescendantId),
    FOREIGN KEY (AncestorId)   REFERENCES dbo.ProductCategory_Closure_Main(CategoryId),
    FOREIGN KEY (DescendantId) REFERENCES dbo.ProductCategory_Closure_Main(CategoryId)
);
GO
INSERT INTO dbo.ProductCategory_Closure_Main (CategoryId, CategoryName)
SELECT CategoryId, CategoryName FROM dbo.ProductCategory_AdjacencyList;
GO
-- 生成闭包关系（自身 + 所有祖先-后代对）
;WITH CategoryTree AS (
    SELECT CategoryId, CategoryId AS AncestorId, 0 AS Depth
    FROM dbo.ProductCategory_AdjacencyList
    UNION ALL
    SELECT ct.CategoryId, al.ParentId, ct.Depth + 1
    FROM CategoryTree ct
    INNER JOIN dbo.ProductCategory_AdjacencyList al ON ct.AncestorId = al.CategoryId
    WHERE al.ParentId IS NOT NULL
)
INSERT INTO dbo.ProductCategory_Closure_Relation (AncestorId, DescendantId, Depth)
SELECT AncestorId, CategoryId, Depth
FROM CategoryTree;
GO
PRINT '闭包表 ProductCategory_Closure_Main / Relation 已创建';
GO

/* =====================================================================
   五、层次模型性能对比查询
   场景: 查询【戒指】分类下所有子节点（含自身）
   ===================================================================== */
PRINT '=== 五、层次模型性能对比 ===';
GO

-- 方案1: 邻接表 - 递归CTE
PRINT '--- 邻接表: 递归CTE查询戒指下所有子节点 ---';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
;WITH CategoryTree AS (
    SELECT CategoryId, CategoryName, ParentId, CAST(CategoryName AS NVARCHAR(500)) AS Path, 0 AS Lvl
    FROM dbo.ProductCategory_AdjacencyList
    WHERE CategoryName = N'戒指'
    UNION ALL
    SELECT c.CategoryId, c.CategoryName, c.ParentId,
           CAST(ct.Path + N'/' + c.CategoryName AS NVARCHAR(500)), ct.Lvl + 1
    FROM dbo.ProductCategory_AdjacencyList c
    INNER JOIN CategoryTree ct ON c.ParentId = ct.CategoryId
)
SELECT CategoryId, CategoryName, Path, Lvl FROM CategoryTree ORDER BY Path;
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

-- 方案2: 嵌套集 - BETWEEN 查询（无需递归）
PRINT '--- 嵌套集: BETWEEN查询戒指下所有子节点 ---';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT child.CategoryId, child.CategoryName, child.LeftValue, child.RightValue
FROM dbo.ProductCategory_NestedSet parent
INNER JOIN dbo.ProductCategory_NestedSet child
    ON child.LeftValue BETWEEN parent.LeftValue AND parent.RightValue
WHERE parent.CategoryName = N'戒指'
ORDER BY child.LeftValue;
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

-- 方案3: 闭包表 - JOIN 查询
PRINT '--- 闭包表: JOIN查询戒指下所有子节点 ---';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT m.CategoryId, m.CategoryName, r.Depth
FROM dbo.ProductCategory_Closure_Relation r
INNER JOIN dbo.ProductCategory_Closure_Main m ON r.DescendantId = m.CategoryId
WHERE r.AncestorId = (SELECT CategoryId FROM dbo.ProductCategory_Closure_Main WHERE CategoryName = N'戒指')
ORDER BY r.Depth, m.CategoryId;
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

-- 性能对比汇总
PRINT '';
PRINT '=== 层次模型选型对比 ===';
PRINT '邻接表:   增删改简单，深树查询需递归，适合层级浅、变动频繁的场景';
PRINT '嵌套集:   子树查询极快(单次BETWEEN)，但插入/移动节点需重排左右值，适合读多写少';
PRINT '闭包表:   查询灵活(祖先/后代/路径都快)，写入需维护关系表，适合复杂树形分析';
GO

/* =====================================================================
   六、Multi-Tenancy 多租户策略
   场景: 珠宝集团SaaS，多个子品牌/加盟商共享系统
   方案: 共享数据库 + 共享Schema + TenantId + 行级安全RLS
   ===================================================================== */
PRINT '=== 六、多租户 ===';
GO
CREATE TABLE dbo.Tenant
(
    TenantId    INT PRIMARY KEY IDENTITY(1,1),
    TenantCode  NVARCHAR(50) NOT NULL UNIQUE,
    TenantName  NVARCHAR(100) NOT NULL,
    IsActive    BIT DEFAULT 1
);
GO
INSERT INTO dbo.Tenant (TenantCode, TenantName, IsActive)
VALUES ('BRAND-A', N'周大福品牌', 1),
       ('BRAND-B', N'老凤祥品牌', 1),
       ('BRAND-C', N'周生生品牌', 1);
GO
CREATE TABLE dbo.Style_MultiTenant
(
    StyleId     INT PRIMARY KEY IDENTITY(1,1),
    TenantId    INT NOT NULL,
    StyleCode   NVARCHAR(50) NOT NULL,
    StyleName   NVARCHAR(100) NOT NULL,
    CreateTime  DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (TenantId) REFERENCES dbo.Tenant(TenantId),
    UNIQUE (TenantId, StyleCode)
);
GO
INSERT INTO dbo.Style_MultiTenant (TenantId, StyleCode, StyleName)
VALUES (1, 'ST-A001', N'周大福传承系列戒指'),
       (1, 'ST-A002', N'周大福迪士尼吊坠'),
       (2, 'ST-B001', N'老凤祥龙凤手镯'),
       (2, 'ST-B002', N'老凤祥黄金项链'),
       (3, 'ST-C001', N'周生生全爱钻戒指');
GO
PRINT '多租户表 Tenant / Style_MultiTenant 已创建，3 个租户 5 条款式';
GO

/* ---------- 行级安全 RLS (Row-Level Security) ---------- */
-- 创建过滤函数
CREATE FUNCTION dbo.fn_TenantFilter(@TenantId INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS AccessFlag
           WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS INT);
GO
-- 创建安全策略
CREATE SECURITY POLICY dbo.Tenant_Style_Policy
ADD FILTER PREDICATE dbo.fn_TenantFilter(TenantId) ON dbo.Style_MultiTenant
WITH (STATE = ON);
GO
PRINT '行级安全策略 Tenant_Style_Policy 已启用';
GO

-- 演示: 模拟租户1登录，只能看到自己的款式
PRINT '--- 模拟租户1(周大福)查询，应只看到2条 ---';
EXEC sp_set_session_context N'TenantId', 1;
SELECT StyleId, TenantId, StyleCode, StyleName FROM dbo.Style_MultiTenant;
GO

-- 演示: 模拟租户2登录
PRINT '--- 模拟租户2(老凤祥)查询，应只看到2条 ---';
EXEC sp_set_session_context N'TenantId', 2;
SELECT StyleId, TenantId, StyleCode, StyleName FROM dbo.Style_MultiTenant;
GO

-- 清除会话上下文（恢复管理员视角需禁用策略或设置为NULL）
EXEC sp_set_session_context N'TenantId', NULL;
GO
-- 临时禁用策略以展示全量数据
ALTER SECURITY POLICY dbo.Tenant_Style_Policy WITH (STATE = OFF);
PRINT '--- 全量数据(RLS已临时关闭) ---';
SELECT StyleId, TenantId, StyleCode, StyleName FROM dbo.Style_MultiTenant;
ALTER SECURITY POLICY dbo.Tenant_Style_Policy WITH (STATE = ON);
GO

/* =====================================================================
   七、综合业务查询示例
   ===================================================================== */
PRINT '=== 七、综合业务查询 ===';
GO

-- 1. 款式完整BOM信息（2NF主表 + Junction关联表 联合查询）
PRINT '--- 款式完整BOM信息 ---';
SELECT
    s.StyleCode,
    s.StyleName,
    g.GemName,
    m.Carat
FROM dbo.Style_2NF s
INNER JOIN dbo.Style_Gem_Map_2NF m ON s.StyleId = m.StyleId
INNER JOIN dbo.Gem_2NF g ON m.GemId = g.GemId
ORDER BY s.StyleCode, g.GemName;
GO

-- 1b. 宝石原料类型查询（3NF + Lookup Table 联合查询）
PRINT '--- 宝石原料及类型(3NF+Lookup) ---';
SELECT
    gr.GemCode,
    gr.WeightCarat,
    gt.GemTypeName,
    gt.Remark
FROM dbo.GemRaw_3NF gr
INNER JOIN dbo.GemType_Lookup_3NF gt ON gr.GemTypeCode = gt.GemTypeCode
ORDER BY gr.GemCode;
GO

-- 2. SCD Type2 历史轨迹查询
PRINT '--- 经销商D001历史变更轨迹 ---';
SELECT
    DealerSurrogateKey,
    DealerName,
    Address,
    StartDate,
    ISNULL(CONVERT(VARCHAR(10), EndDate, 120), N'至今') AS EndDate,
    CASE WHEN IsCurrent = 1 THEN N'当前有效' ELSE N'历史版本' END AS Status
FROM dbo.Dim_Dealer_SCD2
WHERE DealerCode = 'D001'
ORDER BY StartDate;
GO

-- 3. 反范式库存汇总
PRINT '--- 库存汇总(反范式表直接查询，无需JOIN) ---';
SELECT
    StyleName,
    GemName,
    StockQty,
    UnitCost,
    StockQty * UnitCost AS TotalValue
FROM dbo.Inventory_Denormalized
ORDER BY TotalValue DESC;
GO

-- 4. 5NF 三方授权查询: 哪些经销商被授权销售18K白金的某款式
PRINT '--- 5NF三方授权查询 ---';
SELECT
    d.DealerName,
    s.StyleName,
    m.MaterialName,
    CASE WHEN dsm.IsAuthorized = 1 THEN N'已授权' ELSE N'未授权' END AS AuthStatus
FROM dbo.Dealer_Style_Material_5NF dsm
INNER JOIN dbo.Dealer_5NF d ON dsm.DealerId = d.DealerId
INNER JOIN dbo.Style_2NF s ON dsm.StyleId = s.StyleId
INNER JOIN dbo.Material_Lookup m ON dsm.MaterialId = m.MaterialId
ORDER BY d.DealerName, s.StyleName;
GO

/* =====================================================================
   八、建模模式选型速查表
   ===================================================================== */
PRINT '';
PRINT '========================================================';
PRINT '  珠宝行业数据建模模式选型速查表';
PRINT '========================================================';
PRINT '  模式                适用场景                              珠宝行业示例';
PRINT '  ------------------  ------------------------------------  ------------------------';
PRINT '  1NF                 所有关系表基础                        宝石清单每行一颗';
PRINT '  2NF                 复合主键表                            款式-宝石关联表';
PRINT '  3NF                 OLTP业务系统主力                      宝石类型独立Lookup';
PRINT '  BCNF                决定因子非候选键的复杂关系            工序-工艺师-车间';
PRINT '  4NF                 存在独立多值依赖                      款式的宝石和工艺分开';
PRINT '  5NF                 三方及以上不可拆关系                  经销商-款式-材质授权';
PRINT '  反范式              报表/汇总/读多写少                    库存汇总冗余款式名';
PRINT '  Lookup Table        静态字典/枚举                         宝石类型、材质、状态';
PRINT '  Junction Table      多对多关系                            款式-宝石、款式-工序';
PRINT '  SCD Type1           不需要历史的维度                      内部员工部门';
PRINT '  SCD Type2           需要完整历史轨迹(推荐)                经销商、供应商维度';
PRINT '  SCD Type3           只需保留上一次值                      客户最近一次地址';
PRINT '  邻接表              层级浅、变动频繁                       产品分类(3-4层)';
PRINT '  嵌套集              读多写少、子树查询频繁                 珠宝目录导航';
PRINT '  闭包表              复杂树形分析、路径查询                 供应链层级追溯';
PRINT '  多租户(共享Schema)  SaaS多品牌/加盟商(推荐)               珠宝集团ERP';
PRINT '  多租户(独立库)      高保密大客户/合规要求                  银行贵金属部';
PRINT '========================================================';
GO

PRINT '';
PRINT '=== 脚本执行完毕，所有表、数据、查询均已就绪 ===';
GO