Synchronize Categories

Hi,

I am trying to create a type of synchronization but I am having a lot of problems to make this work.

I have 3 tables:

CREATE TABLE Articles (ArticleId INT, ArticleText NVARCHAR(512))

CREATE TABLE Categories (CategoryId INT, CategoryText NVARCHAR(512))

CREATE TABLE CategoriesInCategories (ArticleId INT, CategoryId INT)

Consider the following tables content:

Articles Table

ArticleId ArticleText

1 Game ended 1-1

2 Book Review

Categories Table
CategoryId CategoryText

1 Sports

2 Entertainment

CategoriesInArticles Table

ArticleId CategoryId

1 1

2 2


I need to create the following procedures:

CreateArticle

Create Procedure [CreateArticle]
@ArticleText NVARCHAR(512),
@ArticleCategories NVARCHAR(MAX)
As
Begin
...
End

This procedure should the the following:

1. Create the article in table Articles

2 .Use the function CSVTable that returns a table with all the categories contained in @ArticleCategories.

For each category check if there is already one with the same CategoryText in table Categories.

If there is just get the CategoryId and add a new record into CategoriesInArticles with ArticleId and found CategoryId.

If there isn't any then create the new Category into table Categories and then add the new record to CategoriesInArticles.


DeleteArticle

Create Procedure [DeleteArticle]
@ArticleId INT
As
Begin
...
End

This procedure should the the following:

1. Delete the article in tables Articles and CategoriesInArticles given its ArticleId

2 .Delete categories from table Categories that are no longer associated with any other article (in table CategoriesInArticles)


UpdateArticle
Create Procedure [UpdateArticle]

@ArticleId INT,
@ArticleText NVARCHAR(512),
@ArticleCategories NVARCHAR(MAX)
As
Begin
...
End

This procedure should the the following:

1. Update the values into Articles Table

2 .Use the function CSVTable that returns a table with all the categories contained in @ArticleCategories.

I think the easiest way is to delete all the categories from ArticleId as it is done in DeleteArticle.

Then It creates the new categories as it is done in CreateArticle.

Consider I create a new article:

EXEC CreateArticle @ArticleText = "Book launched at sports event" @ArticleCategories = "Entertainment, Business"

The tables would become:

Articles Table

ArticleId ArticleText

1 Game ended 1-1

2 Book Review

3 Book launched at sports event

Categories Table
CategoryId CategoryText

1 Sports

2 Entertainment

3 Business

CategoriesInArticles Table

ArticleId CategoryId

1 1

2 2

3 2

3 3

NOTE: Because there is no category named Business in Categories this must be created.

Then I would updated it:

EXEC UpdateArticle @ArticleId = "3" @ArticleText = "Book launched at sports event" @ArticleCategories = "Sports, Tech"

The tables would become:

Articles Table

ArticleId ArticleText

1 Game ended 1-1

2 Book Review

3 Book launched at sports event

Categories Table
CategoryId CategoryText

1 Sports

2 Entertainment

4 Tech

CategoriesInArticles Table

ArticleId CategoryId

1 1

2 2

3 1

3 4


Note: Since Business category is no longer used by any other article then it is also deleted from Categories.

The new Tech category is then created and associated with the updated article as well as the existing category Sports.

Finally I would delete it:

EXEC DeleteArticle @ArticleId = "3"

The tables would become:

Articles Table

ArticleId ArticleText

1 Game ended 1-1

2 Book Review

Categories Table
CategoryId CategoryText

1 Sports

2 Entertainment

CategoriesInArticles Table

ArticleId CategoryId

1 1

2 2

Note: Since Tech category is no longer used by any other article then it is also deleted from Categories.

The Sports category is not deleted from Categories since it is used by Article with Id=1

Well, I hope I didn't miss anything and I explained it well.

I have been trying my code, either by creating a separate procedure named SynchronizeCategories or inside the Create, Delete and Update procedure.

Until now I was not able to make this work.

Here is the code I use in my SynchronizeCategories procedure:

INSERT INTO CategoriesInArticles(CategoryId, ArticleId)
SELECT c.CategoryId, @ArticleId
FROM Categories c INNER JOIN CSVTable(@ArticleCategories) ac
ON c.CategoryText = ac.String
LEFT JOIN CategoriesInArticles cia
ON c.CategoryId= cia.CategoryId
WHERE cia.CategoryId IS NULL

INSERT INTO Categories(CategoryId, CategoryText)
SELECT cia.CategoryId, NULL
FROM CategoriesInArticles cia
JOIN [Categories] c ON c.CategoryId = cia.CategoryId
INNER JOIN CSVTable(@ArticleCategories) ac ON ac.String = c.CategoryText
WHERE c.CategoryId IS NULL

DELETE c
FROM Categories c
INNER JOIN CSVTable(@ArticleCategories) ac ON ac.String = c.CategoryText
LEFT JOIN CategoriesInArticles cia ON c.CategoryId = cia.CategoryId
WHERE cia.CategoryId IS NULL

Could someone, please, help me out with this?

Thank You Very Much,

Miguel