Why should you care?

A product has many tags and a tag applies to many products. Putting a Tag column on Products cannot express that cleanly.

See it live — copy this example

Run in SQL Server Management Studio (SSMS) or Azure Data Studio.

USE DataVerse;
IF OBJECT_ID(N'dbo.ProductTags', N'U') IS NOT NULL DROP TABLE dbo.ProductTags;
IF OBJECT_ID(N'dbo.Tags', N'U') IS NOT NULL DROP TABLE dbo.Tags;
CREATE TABLE dbo.Tags (TagId INT IDENTITY PRIMARY KEY, TagName NVARCHAR(50) NOT NULL UNIQUE);
CREATE TABLE dbo.ProductTags (
    ProductId INT NOT NULL,
    TagId INT NOT NULL,
    CONSTRAINT PK_ProductTags PRIMARY KEY (ProductId, TagId),
    CONSTRAINT FK_PT_Product FOREIGN KEY (ProductId) REFERENCES dbo.Products(ProductId),
    CONSTRAINT FK_PT_Tag FOREIGN KEY (TagId) REFERENCES dbo.Tags(TagId)
);
INSERT INTO dbo.Tags (TagName) VALUES (N'wireless'), (N'audio');
INSERT INTO dbo.ProductTags (ProductId, TagId)
SELECT p.ProductId, t.TagId FROM dbo.Products p CROSS JOIN dbo.Tags t WHERE p.Sku = 'HD-100';
SELECT p.Name, t.TagName
FROM dbo.ProductTags pt
JOIN dbo.Products p ON p.ProductId = pt.ProductId
JOIN dbo.Tags t ON t.TagId = pt.TagId;

What happened?

• ProductTags stores pairs only.
• The composite primary key blocks duplicate links.
• The final SELECT lists each product-tag combo.

Practice next

1. Create Tags and ProductTags.
2. Link HD-100 to both tags.
3. Query products for tag wireless.
4. Add TaggedAt DATETIME2 DEFAULT SYSUTCDATETIME() on ProductTags.
5. Delete one tag link and confirm the other remains.

Remember

M:N needs a bridge table. Two FKs + composite PK is the usual pattern. Query through the bridge with two joins.

Catalog tagging

DataVerse merchandising tags products for festival filters.

Outcome: Shoppers filter wireless + audio without denormalized mess.