CODEWITHSUNDEEP
sql-serverdatabase-design
Elias Bandeira Rodrigues Cardo
Elias Bandeira Rodrigues Cardo

Reputation: 27

Tag in SQL Server database

I am creating a website based on question idea (like StackOverflow).

Imagine that similar questions have same tags, and a tag can belong to one or many questions.

I am using a SQL Server database.

Is the right idea to create tables in database to register these tags or is there a different method/concept that I don't know to do so?

Upvotes: 1

Views: 352

Answers (1)

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

If you are not dealing with huge amounts (beyond tens of millions I think) of questions and tags you can go for a design like this one:

Question

QuestionId INT NOT NULL CONSTRAINT PK_Question PRIMARY KEY, -- auto generated with IDENTITY or SEQUENCE
QText NVARCHAR(MAX) NOT NULL

Tag

TagId INT NOT NULL CONSTRAINT PK_Tag PRIMARY KEY, -- auto generated 
TagName NVARCHAR(128)

QuestionXTag

QuestionXTagId INT NOT NULL CONSTRAINT PK_QuestionXTag PRIMARY KEY, -- auto generated 
QuestionId INT NOT NULL CONSTRAINT FK_QuestionXTag_Question FOREIGN KEY REFERENCES Question,
TagId INT NOT NULL CONSTRAINT FK_QuestionXTag_Tag FOREIGN KEY REFERENCES Tag,
CONSTRAINT UQ_QuestionXTag_QT UNIQUE (QuestionId, TagId)

You have an n:n association between Questions and Tags.

So, the short answer is yes, it can be done in SQL Server.

My recommendation is try storing your data in a relational database, unless there is a serious reason not to (very big data, non-homogeneous data etc.), as this way provides meaningful structures and also low-level constraints.

Upvotes: 1

Related Questions