如何在关系型数据库中设计带逻辑操作符的连续实体关联Schema？

Nice question! Modeling ordered collections with inter-item operators is a classic relational design problem, and here's the cleanest, most maintainable schema I'd recommend for your ObjectBlock scenario:

Core Tables #

We'll use three tables to separate concerns while capturing all required relationships, order, and logical operators:

1. ObjectBlock (Top-level Container) #

Stores metadata about each block, with a unique identifier to tie everything together:

CREATE TABLE ObjectBlock (
    block_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL, -- Optional but useful for human identification
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    -- Add any other block-level attributes you need (e.g., description, owner)
);

2. Object (Entity Storage) #

Stores the actual data for each individual Object entity:

CREATE TABLE Object (
    object_id INT PRIMARY KEY AUTO_INCREMENT,
    content TEXT NOT NULL, -- Replace with your actual Object data fields (e.g., parameters, values)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    -- Add any other Object-specific attributes (e.g., type, metadata tags)
);

3. ObjectBlockMembers (Ordered Association with Operators) #

This is the critical table that links Objects to their parent ObjectBlock, enforces sequence order, and stores the logical operator between consecutive Objects:

CREATE TABLE ObjectBlockMembers (
    block_id INT NOT NULL,
    object_id INT NOT NULL,
    position INT NOT NULL,
    next_operator ENUM('AND', 'OR') NULL, -- NULL means no subsequent Object in the block
    PRIMARY KEY (block_id, object_id), -- Ensures an Object can't be added to the same Block twice
    UNIQUE KEY (block_id, position), -- Ensures unique positions per Block (no duplicate order slots)
    FOREIGN KEY (block_id) REFERENCES ObjectBlock(block_id) ON DELETE CASCADE,
    FOREIGN KEY (object_id) REFERENCES Object(object_id) ON DELETE CASCADE
);

Why This Schema Is Optimal #

• Explicit Order Control: The position integer lets you define and modify the sequence of Objects reliably—no risky reliance on insertion order, which can break if data is updated or reinserted.
• Direct Operator Mapping: next_operator stores exactly the logical connector between the current Object and the next one in the sequence. The final Object in the Block gets a NULL value here, clearly signaling the end of the expression.
• Data Integrity: Constraints prevent duplicate entries and ensure referential integrity (if a Block or Object is deleted, associated links are automatically cleaned up).
• Maintainability: Separating concerns keeps each table focused, making it easy to modify Block metadata, Object data, or the sequence/operators independently without disrupting other parts of the system.

Example Usage #

Let's model your sample expression: Object1 AND Object2 OR Object3 AND Object4

1. Insert the association data (assuming block_id = 1, and object_ids are 101, 102, 103, 104):

INSERT INTO ObjectBlockMembers (block_id, object_id, position, next_operator)
VALUES
(1, 101, 1, 'AND'),
(1, 102, 2, 'OR'),
(1, 103, 3, 'AND'),
(1, 104, 4, NULL);

2. Retrieve the full logical structure of the Block:

SELECT 
    o.object_id, 
    o.content, 
    obm.next_operator
FROM ObjectBlockMembers obm
JOIN Object o ON obm.object_id = o.object_id
WHERE obm.block_id = 1
ORDER BY obm.position ASC;

You can reconstruct the exact logical expression by iterating through the results and appending each next_operator between consecutive Objects.

Optional Extension: Handling Grouped Logic #

If you later need to support nested/grouped expressions (e.g., (Object1 AND Object2) OR (Object3 AND Object4)), you can add a group_id or depth column to ObjectBlockMembers to track nested groups. For your current stated requirement, though, the schema above is the most efficient and straightforward solution.

内容的提问来源于stack exchange，提问作者Dacian Mujdar