时间戳

首页 > 解决方案 > 设计具有复杂依赖关系的数据库？（与调度有关）

database-design - 设计具有复杂依赖关系的数据库？（与调度有关）

问题描述

我正在寻求设计一个数据库来存储有关一些复杂依赖项的信息，并且可以真正使用正确的方向。我的谷歌搜索没有发现任何相关信息，但也许你有一个很棒的网络链接。

为了提供上下文，它围绕作业调度并将提供分析。不，我不想创建作业调度程序。

关于数据的一些说明

标签： database-designscheduled-tasks

解决方案

这是其中一种方法：

建议

CREATE TABLE job (
    id integer NOT NULL,
    title character varying NOT NULL,
    CONSTRAINT pk_job
        PRIMARY KEY (id)
);

-- simple job-job dependency, time-based dependency, complex job-job-dependency construct
CREATE TABLE dependency_type (
    id integer NOT NULL,
    title character varying NOT NULL,
    CONSTRAINT pk_dependency_type
        PRIMARY KEY (id)
);
COMMENT ON TABLE dependency_type IS 'simple job-job dependency, time-based dependency, complex job-job-dependency construct';

-- AND, OR, XOR
CREATE TABLE operator_type (
    id integer NOT NULL,
    title character varying NOT NULL,
    CONSTRAINT pk_operator_type
        PRIMARY KEY (id)
);
COMMENT ON TABLE operator_type IS 'AND, OR, XOR';

CREATE TABLE dependency (
    id integer NOT NULL,
    type_id integer NOT NULL,
    job_id integer,
    depend_on_job_id integer,
    depend_on_dependency_id integer,
    operator_type_id integer,
    condition varchar,
    CONSTRAINT pk_dependency
        PRIMARY KEY (id)
);

ALTER TABLE dependency
    ADD CONSTRAINT fk_dependency_depend_on_dependency_id
        FOREIGN KEY (depend_on_dependency_id)
        REFERENCES dependency (id) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE dependency
    ADD CONSTRAINT fk_dependency_depend_on_job_id
        FOREIGN KEY (depend_on_job_id)
        REFERENCES job (id) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE dependency
    ADD CONSTRAINT fk_dependency_job_id
        FOREIGN KEY (job_id)
        REFERENCES job (id) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE dependency
    ADD CONSTRAINT fk_dependency_operator_type_id
        FOREIGN KEY (operator_type_id)
        REFERENCES poperator_type (id) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE RESTRICT;

ALTER TABLE dependency
    ADD CONSTRAINT fk_dependency_type_id
        FOREIGN KEY (type_id)
        REFERENCES dependency_type (id) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE CASCADE;

因此，对于您描述的情况：

否 1.作业 C 依赖于作业 B，而作业 B 又依赖于作业 A

INSERT INTO dependency (type_id, job_id, depend_on_job_id) VALUES (SIMPLE, JOB B, JOB A);
INSERT INTO dependency (type_id, job_id, depend_on_job_id) VALUES (SIMPLE, JOB C, JOB B);

否 2.作业 C 可能依赖于作业 B1 和作业 B2

INSERT INTO dependency (type_id, depend_on_job_id) VALUES (SIMPLE, JOB B2);
INSERT INTO dependency (type_id, job_id, depend_on_job_id, depend_on_dependency_id, operator_type_id) VALUES (COMPLEX, JOB C, JOB B1, DEPENDENCY D1 (from previous insert), AND);

否 3.工作 C 可能依赖于（工作 B1 或工作 B2）和工作 A

INSERT INTO dependency (type_id, depend_on_job_id) VALUES (SIMPLE, JOB B2);
INSERT INTO dependency (type_id, depend_on_job_id, depend_on_dependency_id, operator_type_id) VALUES (COMPLEX, JOB B1, DEPENDENCY D1 (from previous insert), OR);
INSERT INTO dependency (type_id, job_id, depend_on_job_id, depend_on_dependency_id, operator_type_id) VALUES (COMPLEX, JOB C, JOB A, DEPENDENCY D2 (from previous insert), AND);

否 4.工作 C 可能依赖于工作 A 或上午 10 点

INSERT INTO dependency (type_id, condition) VALUES (TIME-BASED, '10 AM');
INSERT INTO dependency (type_id, job_id, depend_on_job_id, depend_on_dependency_id, operator_type_id) VALUES (COMPLEX, JOB C, JOB A, DEPENDENCY D1 (from previous insert), OR);

此模型可能有更新：您可能希望拆分depend_on_job_id为depend_on_dependency_id多个列（depend_on_job1_id、depend_on_job2_id等）。在某些情况下，这会减少树的深度（例如第 2种情况。然后可以只用一个来实现INSERT）。当然，模型会跳过索引、唯一键等。

此外，用 SQL 阅读这棵树是完全不同的故事。

推荐阅读