Searching on keywords

[cydewaze]

I’ve been given a database to build a search form around, and most of the search parameters are pretty straightforward. The one that isn’t so straightforward is a keyword column, which has a comma-delimited list of keywords for each record (i.e. transit,urban,environment) that the content owner would like people to be able to search on.

The draft version of the tool I’ve created uses WHERE CONTAINS(column,‘keyword’) but I’m wondering if that’s the best thing to use. This is SQL Server 2012, and I had to create a fullindex in the database for this to work.

Is there a better way to handle a keyword search? Should I put keywords in their own table and have a table that links keyword ID to record ID?

Keep in mind that none of the content is actually in the database. This is just a list of links to external data, organized by category.

[r937]

[quote=“cydewaze, post:1, topic:114090, full:true”]Is there a better way to handle a keyword search? [/quote]yes, another table, one keyword per row

except don’t use a keyword id, use the keyword itself

[cydewaze]

If I don’t have a keyword ID, how do I tell the main records column which keyword goes with which ID?

I was thinking:

Records:
[recordID][recordTitle]

Keywords:
[keywordID][keyword]

Keyrecords:
[recordID][keywordID]

[r937]

like this –

Records:
[recordID][recordTitle]

Keyrecords:
[recordID][keyword]

[r937]

and if you absolutely must have only approved keywords allowed in the Keyrecords table, then you set up the keyword in Keyrecords to be a FK to this table –

Keywords:
[keyword]

[cydewaze]

@r937

like this –

Records:[recordID][recordTitle]

Keyrecords:[recordID][keyword]

Doesn’t that mean that I would have duplicate keywords in the keywords table, one for each record that keyword applied to? Most of the keywords apply to multiple records.

[DaveMaxwell]

Rudy can override this opinion, but in this case, the overhead required for the additional joins outweigh the small amount of space you’d save by the extra rows. Unless you’ve got MILLIONS of duplicated records, there’s just no benefit to take this extra normalization step.

[r937]

[quote=“cydewaze, post:6, topic:114090”]
Doesn’t that mean that I would have duplicate keywords in the keywords table, one for each record that keyword applied to? [/quote]of course

and of course the same amount of duplication occurs in your comma-delimited keywords

stop and think about it for a minute, this is perfectly okay

for example, consider an employee table that includes, among other things, the employee’s state or province

those states and provinces are duplicated throughout the table, no?

[r937]

@DaveMaxwell

Rudy can override this opinion, but in this case, the overhead required for the additional joins outweigh the small amount of space you’d save by the extra rows. Unless you’ve got MILLIONS of duplicated records, there’s just no benefit to take this extra normalization step.

overriding on the count of 3… 2… 1…

[cydewaze]

@r937

and of course the same amount of duplication occurs in your comma-delimited keywords

Oh definitely! I just figured that if we were moving those keywords to a new table, you’d only want one of each of them.

As far a the joins go, I think joining another table would be less overhead than doing a CONTAINS on the comma-delimited column, no?

[r937]

[quote=“cydewaze, post:10, topic:114090”]As far a the joins go, I think joining another table would be less overhead than doing a CONTAINS on the comma-delimited column, no?
[/quote]yes!