3

NOTE: The application is not really about websites or keywords, I am using that here only to provide reference points for discussion.

I have a data set that is really just a string of words. Think of at "SEO" keywords for a website. I have a table that contains data that relates to this data. For example, say a list of websites.

The key scenario for this data set is retrieval against the master data, in two-way fashion. That is, while I can get a website with all its associated keywords, I should also be able to input a keyword and get a list of websites attached to that keyword. There is no other metadata attached (or attachable) to the keyword itself.

Now, given that query performance while retrieving the website -> keyword and keyword -> website (significantly more use cases) is paramount, which of these design scenarios makes more sense?

  1. In the websites table, I have a single nvarchar column that contains a string of all the keywords for that website, possibly comma-separated. Retrieval in this case would be using a LIKE operator on that row.

  2. I create a separate keywords table, with two columns (Id, Keyword), put all the keywords in there, and then have a third table WebsiteKeywords that contain the mappings between the websites and keywords tables. To retrieve, we do a three-way join between websites, websitekeywords and keywords tables.

The retrieval is designed to happen via web service infrastructure, so there will be multiple look ups from the middle-tier layer before it thinks it has all the data. So a single "search" fired as a call will result in multiple such look ups, all results will be aggregated by that middle tier before being returned to the caller.

What are your suggestions?

  • 4
    Option 2. If performance is a concern in any way at all, you definitely don't want to go down the road of using LIKE to search for CSV values in a single column. – LowlyDBA Feb 24 '15 at 19:30
  • 3
    Also see Why Normalization? – LowlyDBA Feb 24 '15 at 19:40
  • 1
    If you are thinking of LIKE at least consider Full Text Indexes instead. But if Full Text Indexesa are not needed for some reason, then as John M noted, normalization, Primary and foreign keys, etc. is the best for performance. – RLF Feb 24 '15 at 20:02
3

Definitely you want to use option 2. Not only will your queries be faster (= is always faster than like) but you can also index on that keyword field for even faster queries AND your storage space will be significantly reduced since you are not storing the long keyword strings for each website.

2

Option 1 will always force a O(N) operation (a table/index scan) which disqualifies this option for significant amounts of data. You must use option 2 if you want a solution that scales with the amount of data.

If there are very few rows (such as 3 or so) option 1 might actually be faster. I doubt that we are talking about that case here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy