Παράδειγμα Anti‑Pattern

-- Parent tables
CREATE TABLE dbo.Orders (
    order_id      int PRIMARY KEY,
    order_number  nvarchar(50) NOT NULL
);

CREATE TABLE dbo.Products (
    product_id    int PRIMARY KEY,
    product_name  nvarchar(200) NOT NULL
);

-- Πολυμορφικός πίνακας σχολίων (ANTI-PATTERN)
CREATE TABLE dbo.Comments (
    comment_id    int IDENTITY PRIMARY KEY,
    target_id     int NOT NULL,           -- δείχνει είτε σε Orders είτε σε Products
    target_type   varchar(20) NOT NULL,   -- 'Order' ή 'Product'
    comment_text  nvarchar(1000) NOT NULL,
    created_at    datetime2(0) NOT NULL DEFAULT sysdatetime()
    -- ΠΡΟΣΟΧΗ: Δεν υπάρχει FK εδώ!
);

-- Εισαγωγή δεδομένων (με ορφανό παράδειγμα)
INSERT dbo.Orders(order_id, order_number) VALUES (1, N'ORD-001');
INSERT dbo.Products(product_id, product_name)   VALUES (10, N'Keyboard');

INSERT dbo.Comments(target_id, target_type, comment_text)
VALUES
  (1,  'Order',   N'Πρώτο σχόλιο για την παραγγελία 1'),
  (10, 'Product', N'Σχόλιο για το προϊόν 10'),
  (999, 'Order',  N'Ορφανό σχόλιο (Order 999 δεν υπάρχει)');

Ενδεικτικό query (δύσχρηστο & εύθραυστο)

SELECT
    c.comment_id,
    c.comment_text,
    c.target_type,
    COALESCE(o.order_number, p.product_name) AS parent_display
FROM dbo.Comments AS c
LEFT JOIN dbo.Orders   AS o ON c.target_type = 'Order'   AND c.target_id = o.order_id
LEFT JOIN dbo.Products AS p ON c.target_type = 'Product' AND c.target_id = p.product_id;

Ξεχωριστά Association Tables (ρητά FKs)

CREATE TABLE dbo.OrderComments (
    comment_id   int IDENTITY PRIMARY KEY,
    order_id     int NOT NULL FOREIGN KEY REFERENCES dbo.Orders(order_id),
    comment_text nvarchar(1000) NOT NULL,
    created_at   datetime2(0) NOT NULL DEFAULT sysdatetime()
);

CREATE TABLE dbo.ProductComments (
    comment_id   int IDENTITY PRIMARY KEY,
    product_id   int NOT NULL FOREIGN KEY REFERENCES dbo.Products(product_id),
    comment_text nvarchar(1000) NOT NULL,
    created_at   datetime2(0) NOT NULL DEFAULT sysdatetime()
);

Εναλλακτική Β: Unified Base Entity (Table‑per‑Type με κοινό entity_id)

CREATE TABLE dbo.Entities (
    entity_id   bigint IDENTITY PRIMARY KEY,
    entity_type varchar(20) NOT NULL -- 'Order','Product',...
);

CREATE TABLE dbo.Orders (
    order_id     bigint PRIMARY KEY,        -- == entity_id
    order_number nvarchar(50) NOT NULL,
    CONSTRAINT FK_Orders_Entity FOREIGN KEY (order_id)
        REFERENCES dbo.Entities(entity_id)
        ON DELETE CASCADE
);

CREATE TABLE dbo.Products (
    product_id    bigint PRIMARY KEY,       -- == entity_id
    product_name  nvarchar(200) NOT NULL,
    CONSTRAINT FK_Products_Entity FOREIGN KEY (product_id)
        REFERENCES dbo.Entities(entity_id)
        ON DELETE CASCADE
);

CREATE TABLE dbo.Comments (
    comment_id   bigint IDENTITY PRIMARY KEY,
    entity_id    bigint NOT NULL
        FOREIGN KEY REFERENCES dbo.Entities(entity_id) ON DELETE CASCADE,
    comment_text nvarchar(1000) NOT NULL,
    created_at   datetime2(0) NOT NULL DEFAULT sysdatetime()
);