database design advice - custom searching

I currently run a website where my users can fill out a profile as well as information on different types of services that they offer. For each "offer" that they input, there is an optional tags field, which I want to use to help with searching. Right now I have it stored as a nvarchar field in the main OFFERS table as a comma separated list.

Would it be better to move it to it's own table, split up the comma separated list, and have each term be it's own record in the table? So, instead of:

Main OFFERS table that contains all of the data for the offer
offerID      Tags
100            web,design,html

to

a TAGS table
offerID      Tag
100            web
100            design
100            html

I'm looking for the most efficient manner, as I can see this table growing pretty quickly. I'm thinking this way would be better as using a LIKE statement is not efficient. Am I correct in thinking this way, or is there a better way to do it?

I am currently running Sql Server 2012
LVL 34
Big MontyWeb Ninja at largeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Yes, I would definitely tokenize the tags and place them in a table of their own.  If you expect a lot of overlap in your entries then I would make that table hold unique entries and create an additional joining table between the tags and the offer to prevent over-bloating the table.  Once you have isolated the tags in their own table then you can leverage full-text indexing to create performant searches.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Yes, and go from nvarchar to varchar if you can, unless you absolutely need Unicode.
Big MontyWeb Ninja at largeAuthor Commented:
If you expect a lot of overlap in your entries then I would make that table hold unique entries and create an additional joining table between the tags and the offer

so if I had:
new TAGS table
tagID       Tag
1               web
2               design
3               html

then I would create a new join table like:

TagID       OfferID
1                100
2                100
3                100

and keep the TAGS table containing unique tags, is that what I'm understanding?

@SP - are there performance reasons why to use one or the other? I struggled with which one to use, and chose nvarchar for reasons I don't remember right  now :) I do know I use nvarchar( max ) instead of TEXT, as I read that it optimizes the data better, but for smaller fields I don't remember why I chose it
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Scott PletcherSenior DBACommented:
nvarchar is two bytes per character; varchar is 1.

So a name like "Steven" would take 12 bytes for nv vs 6 for v.

If you use full-text indexing, you can use the actual columns from a TAGS combined table directly.

If you don't use f-t, you should create a tags "master" table and then an intersection table to combine offers and tags.  Something like this:

Offers ( offer_id )
Tags ( tag_id int identity(1, 1) not null, tag nvarchar(100) )
OfferTags ( offer_id, tag_id )
Brian CroweDatabase AdministratorCommented:
Big Monty,

Yes, that is how I would set it up.  It may be overkill depending on the scale of your application but it will work.  You can use CONTAINSTABLE or FREETEXTTABLE functions (once you have set up the full-text indexing on the TAGS.Tag column) to return the matching key values which will then join easily to the referenced offers via the OfferTag table.
Big MontyWeb Ninja at largeAuthor Commented:
thanks for the advice
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.