相关文章数据库设计

数据库设计

Information表

字段:InformationID,InfoContent,...

KeyWord表(存储单个关键字)

字段:KeyWordID,KeyWord

Relation表

字段:InformationID,KeyWordID

 

相应的存储过程

AddKeyword：往KeyWord表添加新增的单个关键字,往Relation表添加关联

CREATE procedure dbo.AddKeyword @InformationID INT, @KeyWord NVARCHAR(50) AS SET NOCOUNT ON DECLARE @KeyWordID INT SELECT @KeyWordID=KeyWordID FROM KeyWord WHERE KeyWord=@KeyWord IF @KeyWordID IS NULL BEGIN INSERT INTO KeyWord (KeyWord) VALUES(@KeyWord) SET @KeyWordID=IDENT_CURRENT('KeyWord') END INSERT INTO Relation (InformationID, KeyWordID) VALUES(@InformationID, @KeyWordID) SET NOCOUNT OFF GO  

 

UpdateRelation：Relation表更新文章和关键字关联

 

晚上继续...

 

SELECT DISTINCT *
FROM Information AS i
INNER JOIN Relation AS r ON i.InformationID = r.InformationID
INNER JOIN KeyWord AS k ON r.keywordID = k.KeyWordID
WHERE (i.InformationID <> 1) and r.keywordID in
(select r.keywordID from relation r where r.informationID=1)