0
\$\begingroup\$

I'm not going to lie - Sql Server is not in the top 10 of my skill set, but it's something that I have to do from time to time.

What's more, I'm using Entity Framework, so there is a workaround required because it doesn't yet support foreign key relationships.

In this scenario, I have 3 tables, representing some content, stacked in a 'grouped'. relationship. I use the term grouped because a child might be in multiple groups rather than just being associated with a single parent.

TopicKeywords

It contains info about keywords.

A keyword can only be assigned to a one page but a page can have multiple keywords associated with it.

CREATE TABLE [dbo].[TopicKeyword] (
    [Id]      SMALLINT      NOT NULL,
    [Keyword] VARCHAR (100) NOT NULL,
    [Volume]  INT           NOT NULL,
    [PageId]  SMALLINT      NOT NULL,
    CONSTRAINT [PK_TopicKeyword] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [AK_TopicKeyword_Keyword] UNIQUE NONCLUSTERED ([Keyword] ASC),
    CONSTRAINT [FK_TopicKeyword_PageMeta] FOREIGN KEY ([PageId]) REFERENCES [dbo].[PageMeta] ([Id])
);

GO
CREATE NONCLUSTERED INDEX [IX_TopicKeyword_PageId]
    ON [dbo].[TopicKeyword]([PageId] ASC);

GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_TopicKeyword_Id_PageId]
    ON [dbo].[TopicKeyword]([Id] ASC, [PageId] ASC);

The next two tables represent the parent child relationship.

TopicContent (child)

Contains keywords for a 'Cluster'.

KeywordId must exist in the TopicKeywords table. ParentKeywordId must exist in the TopicCluster table.

Each ParentKeywordId/KeywordId combination must be unique - also, they can't both be the same thing.

CREATE TABLE [dbo].[TopicContent] (
    [ParentKeywordId] SMALLINT NOT NULL,
    [KeywordId]       SMALLINT NOT NULL,
    CONSTRAINT [PK_TopicContent] PRIMARY KEY CLUSTERED ([ParentKeywordId] ASC, [KeywordId] ASC),
    CONSTRAINT [FK_TopicContent_TopicCluster] FOREIGN KEY ([ParentKeywordId]) REFERENCES [dbo].[TopicCluster] ([KeywordId]),
    CONSTRAINT [FK_TopicContent_TopicKeyword] FOREIGN KEY ([KeywordId]) REFERENCES [dbo].[TopicKeyword] ([Id]),
    CONSTRAINT [CK_TopicContent_ParentKeywordId] CHECK ([ParentKeywordId]<>[KeywordId])
);

GO
CREATE NONCLUSTERED INDEX [IX_TopicContent_KeywordId]
    ON [dbo].[TopicContent]([KeywordId] ASC);

TopicCluster (parent)

Contains the cluster/parent/group entity.

The KeywordId must exist in the TopicKeywords table, and the KeywordId/PageId combination must be valid.

KeywordId must be unique.

A page (PageId) can only own one TopicCluster, so it must in theory, be unique.

CREATE TABLE [dbo].[TopicCluster] (
    [KeywordId] SMALLINT NOT NULL,
    [PageId]    SMALLINT NOT NULL,
    CONSTRAINT [PK_TopicCluster] PRIMARY KEY CLUSTERED ([KeywordId] ASC),
    CONSTRAINT [AK_TopicCluster_PageId] UNIQUE NONCLUSTERED ([PageId] ASC),
    CONSTRAINT [FK_TopicCluster_TopicKeyword] FOREIGN KEY ([KeywordId]) REFERENCES [dbo].[TopicKeyword] ([Id]),
    CONSTRAINT [FK_TopicCluster_TopicKeyword2] FOREIGN KEY ([KeywordId], [PageId]) REFERENCES [dbo].[TopicKeyword] ([Id], [PageId])
);

GO
CREATE NONCLUSTERED INDEX [IX_TopicCluster_KeywordId_PageId]
    ON [dbo].[TopicCluster]([KeywordId] ASC, [PageId] ASC);

This is my 'novice' attempt. I'd appreciate any advice and suggestions for improvement.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.