For each page in my site I am intending a user can add any tags they think relevant. Then another page will enable them to search for pages with a tag e.g. they could search for football to find all pages tagged with football.

I have a table that lists all the pages, should I add another column to this called 'tags', or should the tags be a seperate table with a different row for each tag? If anyone can advise how the table(s) be setup for this, I'd greatly appreciate it!

Dave

I would just add the tags field as a new field for the pages table. That's not the normalized version but I think doing a new table "tags" and then another new tables "pagetags" would be a bit overkill in my opinion.

The way you want to do it is create a table

Tags

id (primary key), tag (varchar tag), searchedOn (if you want to show which tags are getting the most attention..

Then another table

Pages_Tags

page_id (foreign key to page table id), tag_id(foreign key to tag table id), tag_relevance (if you want to track how many times each page was tagged with a certain tag)

Then everytime a user adds a tag to a page you create a row in the tags table (if it doesn't exist already) and then add an entry to Pages_Tags with the page_id and tag_id (if there isn't one already.

You just increase the tag relevance if it does already exist.

By adding a column to the pages table your data will quickly become redundant as numerous pages contain exactly the same tags but for no benefit.

Thanks for your replies, I am organising my tables in the way Rakuli suggested. Thanks for your detailed answer Rakuli, it gave me a much better understanding of how Relational databases are meant to work.

Dave

Just another quick question, I have a pages table that contains info on the pages, the pages_tags lookup table and the tags table.
When getting the info from the database for display on a page I presume I should use 2 seperate queries - one for getting the tags for that page and one for getting the page info for that page. Otherwise I get multiple rows with a different tag for that page in each row but the same info from the pages table?

Dave