4

I wanted to create a database that will store the tags that users enter for their questions and then display them all for each individual question posted; something like here on SO.

Here is the table that does everything for me now:

CREATE TABLE questions_tags (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
url TEXT NOT NULL,
tag VARCHAR(255) NOT NULL,
count INT NOT NULL,
PRIMARY KEY (id)
);

I know this is not correct. What other table or tables do I need and what do I need to change to this table if needed?

2
  • Is there any relation to a questions table? Or is questions_tags the only table you have? Commented Dec 3, 2009 at 9:44
  • The questions_tags is all that I have right know, that is why I'm asking. Commented Dec 3, 2009 at 9:46

3 Answers 3

7

You should split your data between two tables, questions and tags and relate them using a questions_tags join table.

CREATE TABLE questions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  url TEXT NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE tags (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tag VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE questions_tags (
  question_id INT UNSIGNED NOT NULL REFERENCES questions,
  tag_id INT UNSIGNED NOT NULL REFERENCES tags
);

I'm not sure what the count column in your original table is for so I skipped it.

Using the above tables you can use joins to find all questions with a certain tag or all tags of a question.

Edit

To get the count for each tag you could to something like this:

  SELECT tag,
         count(*) AS c
    FROM tags
GROUP BY tag;

Edit

To get the counts of all tags for all questions do this:

  SELECT t.tag,
         q.question_id,
         count(*) AS c
    FROM tags AS t,
         questions_tags AS qt
         questions AS q
   WHERE t.id = qt.tag_id
     AND qt.question_id = q.id         
GROUP BY t.id, q.id;

If you want only the count for specific tags or questions add additional WHERE clauses.

Note: All SQL above is untested.

Sign up to request clarification or add additional context in comments.

5 Comments

The count column counts how many times each tag has been entered for example, lets say HTML has been entered 20 times for that question.
Such a count column cotains redundant information and should not be used.
Then what should I use to keep track of how many times the tag has been entered for each question? For example lets say question has HTML entered for it 5 times while questions 2 has HTML entered for it 2 times?
look at my answer there is a request to Count how many times a tag has been used
Although it could be argued that for the sake of speeding up the SELECT query, it might be worthwile having a count column. Aggregate functions such as count() do take extra processing time and as far as I know, are not cached. Since the tags SELECT query can be expected to be run very often, it is worthwile to make it as light and fast as possible.
2

Tables:

CREATE TABLE questions_tags (
tag_id INT INT NOT NULL,
question_id INT NOT NULL,
);

CREATE TABLE tags (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
tag VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);

SQL

Select the tag related to a question

SELECT     tag_id
FROM         question_tags
WHERE     question_id = $yourquestionid'

Select the questions related to a tag

SELECT     question_id
FROM         question_tags
WHERE     tag_id = $yourtagid'

Count how many times a tag has been used

SELECT COUNT(tag_id) 
FROM question_tags 
where tag_id=$yourtagid

Make a tag cloud

SELECT COUNT(tag_id)
FROM question_tags 
GROUP BY tag;

5 Comments

The count increments the tags to display them bigger and the url catches the pages url.
all this things will be in the tag table
you will get the count by doing a simple sql count request.
So how will the tag table look like and which table will be used to display the tags on the page?
I assume the tags table will capture the count of each tag?
0

You would probably need a n:m relation table that stores the relations to your "question"-Table.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.