mysql - 如何根据这个json设计一个表

Question

我需要一些组织数据库的帮助。坦白说，我真的不知道自己在做什么，但我确实知道结果。目前，我不知道如何设计我的数据库。

JSON 源是 Jasmine Matchers(protractor) 记者的输出，将其添加到数据库的原因是为了存储我的 protractor 测试结果。我是一名自动化开发人员，拥有一些表达和角度的知识，但不多。基本上，我正在尝试建立一个记者并学习。

我已经知道如何将数据推送到数据库，我链接了它们，但我真的不知道如何构建数据库。

我有以下 JSON。但是我的数据库中需要哪些列？

{
    "suite1": {
        "id": "suite1",
        "description": "test suite for home",
        "fullName": "test suite for home",
        "failedExpectations": [],
        "status": "finished",
        "specs": [
            {
                "id": "spec0",
                "description": "should be able to login and see the homepage",
                "fullName": "test suite for home should be able to login and see the homepage",
                "failedExpectations": [],
                "passedExpectations": [],
                "pendingReason": "",
                "status": "passed"
            },
            {
                "id": "spec1",
                "description": "should be able to change stuff on homepage",
                "fullName": "test suite for home should be able to change stuff on homepage",
                "failedExpectations": [
                    {
                        "matcherName": "toContain",
                        "message": "Expected undefined to contain 'asd'.",
                        "stack": "Error: Failed expectation\n    at Object.<anonymous> (C:\\Users\\clain\\Documents\\dev\\mat\\test\\e2e\\specs\\test1__spec.js:30:21)\n    at C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\jasminewd2\\index.js:112:25\n    at new ManagedPromise (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:1067:7)\n    at ControlFlow.promise (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:2396:12)\n    at schedulerExecute (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\jasminewd2\\index.js:95:18)\n    at TaskQueue.execute_ (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:2970:14)\n    at TaskQueue.executeNext_ (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:2953:27)\n    at asyncRun (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:2860:25)\n    at C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:676:7\n    at process._tickCallback (internal/process/next_tick.js:109:7)",
                        "passed": false,
                        "expected": "asd"
                    },
                    {
                        "matcherName": "toContain",
                        "message": "Expected undefined to contain 'asd'.",
                        "stack": "Error: Failed expectation\n    at Object.<anonymous> (C:\\Users\\clain\\Documents\\dev\\mat\\test\\e2e\\specs\\test1__spec.js:31:21)\n    at C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\jasminewd2\\index.js:112:25\n    at new ManagedPromise (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:1067:7)\n    at ControlFlow.promise (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:2396:12)\n    at schedulerExecute (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\jasminewd2\\index.js:95:18)\n    at TaskQueue.execute_ (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:2970:14)\n    at TaskQueue.executeNext_ (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:2953:27)\n    at asyncRun (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:2860:25)\n    at C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:676:7\n    at process._tickCallback (internal/process/next_tick.js:109:7)",
                        "passed": false,
                        "expected": "asd"
                    },
                    {
                        "matcherName": "toContain",
                        "message": "Expected undefined to contain 'asd'.",
                        "stack": "Error: Failed expectation\n    at Object.<anonymous> (C:\\Users\\clain\\Documents\\dev\\mat\\test\\e2e\\specs\\test1__spec.js:32:21)\n    at C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\jasminewd2\\index.js:112:25\n    at new ManagedPromise (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:1067:7)\n    at ControlFlow.promise (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:2396:12)\n    at schedulerExecute (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\jasminewd2\\index.js:95:18)\n    at TaskQueue.execute_ (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:2970:14)\n    at TaskQueue.executeNext_ (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:2953:27)\n    at asyncRun (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:2860:25)\n    at C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:676:7\n    at process._tickCallback (internal/process/next_tick.js:109:7)",
                        "passed": false,
                        "expected": "asd"
                    },
                    {
                        "matcherName": "toContain",
                        "message": "Expected undefined to contain 'asd'.",
                        "stack": "Error: Failed expectation\n    at Object.<anonymous> (C:\\Users\\clain\\Documents\\dev\\mat\\test\\e2e\\specs\\test1__spec.js:33:21)\n    at C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\jasminewd2\\index.js:112:25\n    at new ManagedPromise (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:1067:7)\n    at ControlFlow.promise (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:2396:12)\n    at schedulerExecute (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\jasminewd2\\index.js:95:18)\n    at TaskQueue.execute_ (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:2970:14)\n    at TaskQueue.executeNext_ (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:2953:27)\n    at asyncRun (C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:2860:25)\n    at C:\\Users\\clain\\Documents\\dev\\mat\\node_modules\\selenium-webdriver\\lib\\promise.js:676:7\n    at process._tickCallback (internal/process/next_tick.js:109:7)",
                        "passed": false,
                        "expected": "asd"
                    }
                ],
                "passedExpectations": [],
                "pendingReason": "",
                "status": "failed"
            }
        ]
    },
    "suite2": {
        "id": "suite2",
        "description": "test suite for dashboard",
        "fullName": "test suite for dashboard",
        "failedExpectations": [],
        "status": "finished",
        "specs": [
            {
                "id": "spec2",
                "description": "should be able to login and see the dashboard",
                "fullName": "test suite for dashboard should be able to login and see the dashboard",
                "failedExpectations": [],
                "passedExpectations": [],
                "pendingReason": "",
                "status": "passed"
            },
            {
                "id": "spec3",
                "description": "should be able to change options",
                "fullName": "test suite for dashboard should be able to change options",
                "failedExpectations": [],
                "passedExpectations": [],
                "pendingReason": "",
                "status": "passed"
            }
        ]
    }
}

Answer 1

首先，您需要知道特定的数据库设计问题没有唯一的解决方案，并且可以根据使用的技术或数据量等因素找到多种方法，仅举一些。

就您而言，一种简单的方法可以定义三个表:一个用于存储套件，另一个用于存储规范，最后一个用于存储期望。它们每个都有一个与 JSON 中的字段相对应的字段。此外，这些表之间也会有关联。 “Suites”表将与“Specs”建立一对多关系。这样，我们就定义了一个套件，您将拥有许多“规范”。同样，“规范”表将与“期望”表建立一对多关系，确保对于一个规范您会有多个期望。

现在，您必须注意两件事:

1. 既然您说“我有兴趣存储套件全名和所有规范详细信息”，我就没有包含任何对“Suits”表的“期望”引用，即使 JSON 具有包含以下内容的字段:那个标签。从两个相关表引用同一个表将在设计中引入循环，这在某些情况下可能是有害的。

2. 此外，您还可以看到我刚刚为“期望”创建了一个表，而没有对“失败”或“通过”进行任何区别。使用 Passed Boolean(SQL Server 中的 BIT)字段可以轻松实现这种区别。它将定义如果值为 True 则期望通过，如果为 False，则定义是否失败。

我在表创建中包含了一些 SQL Server 代码，以支持所描述的方法:

CREATE TABLE Suites (
    SuiteId NVARCHAR(20) NOT NULL,
    Description NVARCHAR(max),
    FullName NVARCHAR(max),
    Status NVARCHAR(20) NOT NULL,
    PRIMARY KEY (SuiteId),
);

CREATE TABLE Specs  (
    SpecId NVARCHAR(20) NOT NULL,
    Description NVARCHAR(max),
    FullName NVARCHAR(max),
    PendingReason NVARCHAR(max),
    Status NVARCHAR(20) NOT NULL,
    PRIMARY KEY (SpecId),
    CONSTRAINT FK_SpecsSuite FOREIGN KEY (SuiteId)
    REFERENCES Suites(SuiteId)
);

CREATE TABLE Expectations  (
    ExpId int NOT NULL AUTO_INCREMENT,
    MatcherName NVARCHAR(50),
    Expected NVARCHAR(MAX),
    Message NVARCHAR(max),
    Stack NVARCHAR(max),
    Passed BIT,
    PRIMARY KEY (ExpId),
    CONSTRAINT FK_ExpectationsSpec FOREIGN KEY (SpecId)
    REFERENCES Specs(SpecId)
);