I am trying to build a small web application. The application is to store the bookmarks and its tags.
bookmark: www.javaworld.com
tags: java, programming
bookmark: www.jquery.com
tags: java,webapps
now my search criteria is based on tags. If a user search's for java it should give all the bookmarks he stored with java. here both www.javaworld.com and www.jquery.com must be shown.
my database tables looks like this
Users ( UID, Uname, Password)
Bookmarks( BID, UID, bookmark_url,tag1,tag2,tag3,tag4,tag5)
or
Bookmarks( BID,UID,bookmark)
Tags(TID,BID,Tagname)
here BID and TID are surrogate keys.
please suggest the better way to solve this problem considering the scenario i gave..
Why not make something similar like on Stackoverflow? Tags are "global" and not bound to a single bookmark. Allows you to define tags without having to define bookmarks beforehand, which improves flexibility. Also limiting to 5 tags as in your first solution is not a good idea either.
Separating them like follows allows you to run aggregrate functions better (making tag clouds) and you can use the tag table to display tags in a checkbox/multiple select for new bookmarks better. It'd feel awkward if they are bound to another bookmark already.
Table: User [UID, Uname, PW]
Table: Bookmark [BID, UID, url]
Table: Tag [TID, Tagname]
Table: Bookmark_Tag [BID, TID]