CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008database-schema
jwimmer
jwimmer

Reputation: 209

Database schema for posts with multiple tags

I am building the typical post and tag application where a post can have multiple tags. I am stuck on setting up the database schema. I have so far:

Post:
POST_ID PRIMARY KEY
POST_TITLE
POST_BODY
POST_DATE
POST_USERID

Tag:
TAG_ID PRIMARY KEY
TAG_TAGNAME


PT:
PT_ID
PT_POSTID
PT_TAGID


When a user submits a post, I insert the form data into the post table. Next step I loop through the tagnames the user provided and see if they are in the TAG_TAGNAME field. If there is a match, grab the ID and insert it into the PT table. ELSE insert the name into the tag table and grab the ID and insert it into the PT table.Both are along with the postid generated in the POST table insert.
Finally I have a view called PTVIEW with the following schema:

SELECT *
FROM dbo.PT
JOIN Post
ON PT_PostID = dbo.Post.POST_ID
JOIN Tag
ON PT_TagID = tag.TAG_ID

Here is a sample result of select * from PTVIEW
enter image description here


Problem is I can’t get a view of the unique posts along with their tags.
I am using MSSQL so I DO NOT have the Group_concat function built into mySQL.

I can’t build the initial page that will show each post along with its correlating tags like stackoverflow does on the homepage. What am I doing wrong in the PTVIEW ?

Upvotes: 0

Views: 1742

Answers (2)

jwimmer
jwimmer

Reputation: 209

Here is how I learned to do it. 

  SELECT * 
    ,(
        SELECT TAG_TAGNAME+ ', '
        from PT
        JOIN Tag ON PT_TagID = TAG_ID
        Where PT_PostID = POST_ID
        ORDER BY TAG_TAGNAME
        FOR XML PATH('')
    ) AS Tags
    FROM Post

Upvotes: 1

souplex
souplex

Reputation: 981

You basically have 2 options:

  1. Split it up into 2 different queries, get a post, then get it's associated tags.
  2. Concatenate the tags into a single field using a statement like this:
SELECT DISTINCT
        P.Post_ID ,
        SUBSTRING(( SELECT  ',' + T.TAG_TAGNAME AS [text()]
                    FROM    dbo.PT PT
                            INNER JOIN dbo.Tag T ON PT.PT_TAGID = T.TAG_ID
                    WHERE   P.POST_ID = PT.POST_ID
                  FOR
                    XML PATH('')
                  ), 2, 1000) [Tags]
FROM    dbo.Post P

Even though the second option looks kinda nifty, the first option has my personal preference. I think the second is not very readable and performance would not be very good on larger datasets.

Upvotes: 2

Related Questions