Normalise this Table?

Question

Hello all,

I am creating a social bookmarking app. I am having a re-thought of the DB design in the middle of development.

Should I normalise the bookmarks table and remove the tag columns that I have into a separate table. I have 10 tags per bookmark and therefore 10 columns per record (per bookmark).

It seems to me that breaking the table into two would just mean I would have to do a join but the way I currently have it, its a straight select - but the table doesn't feel right...?

Thanks all

Update

Current Table Structure:

CREATE TABLE IF NOT EXISTS `bookmarks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` char(250) NOT NULL,
  `link` text NOT NULL,
  `keyword_1` char(250) NOT NULL,
  `keyword_2` char(250) NOT NULL,
  `keyword_3` char(250) NOT NULL,
  `keyword_4` char(250) NOT NULL,
  `keyword_5` char(250) NOT NULL,
  `keyword_6` char(250) NOT NULL,
  `keyword_7` char(250) NOT NULL,
  `keyword_8` char(250) NOT NULL,
  `keyword_9` char(250) NOT NULL,
  `keyword_10` char(250) NOT NULL,
  `date_added` datetime NOT NULL,
  `privacy_type` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=68 ;

Just to add, a bookmark belongs to user, in addition a tag belongs to a bookmark. So should I add the user_id to the tags table?

A new related question:

How would you search for a tag and return the bookmark?

Great to see a SQL Query example. I am hoping for something efficient as I currently can't think of anything decent apart from doing a LIKE test on every keyword column!

Answer 1

I would normalize it. If you don't use tags on every bookmark that would increase some performance. And that would give you some more flexibility for example put on a Tagg-cloud thingy and just fetch All different tags.

Answer 2

Plan for the future right now they are just tags, but they may end up carrying properties or attributes that build upon the tag. If you stored them seperately this would be much simpler, a join would bring the data together. If you just use them in one table and you need to add to this table it becomes a nightmare. Normalize the data by setting it up with it's own entity table. It's like good OOP, a lot more work upfront but pays dividends in the end :).

Answer 3

I think that normalizing the tags into their own table will lead to fewer lines of code and more easily allow for expanding the number of tags without revisiting your code.

This also allows you to more easily query the tags independently, as a group, such as determining the ten most popular tags, etc.

SELECT key_word, count(bookmark_id) AS tag_frequency
FROM tags
GROUP BY key_word
ORDER BY count(bookmark_id) DESC LIMIT 10

From an object oriented approach, normalizing the tags into their own table means that tag1, tag2, etc. are no longer attributes of bookmark, instead tags becomes an attribute of bookmark, which is a collection of tag.

You will probably actually be writing less lines of code, though you'll be handling things a bit differently.

For example, first, you'll look up the bookmark and it's attributes. Then, if you want to get a list of tags for the bookmark, you'll do another query against the tags table. You're probably not going to do a join for searching in this direction.

Then, you'll just iterate through the list of tags and do the same processing on each tag, rather than repeating lines of code for each tag.

However, you will use a join to find a bookmark for a tag:

SELECT bookmarks.id, bookmarks.title
FROM tags
JOIN bookmarks ON bookmarks.id = tag.bookmark_id
WHERE tags.key_word = ?

Of course, this may return more than one record.