mysql - keyword search table - a good idea?

Question

Is it a good idea to have a 'search table'?

for example a search that can search 'users', 'groups' and 'pages' (facebook style) would have fields like keywords, userid, groupid, pageid

that way the system can do a LIKE query on the keywords from one table.

or would it be better like

keyword1, keyword2, keyword3, keyword4, keyword5, userid, groupid, pageid

or

keeping the keywords in each of the tables (user, group and page)... but I am quite sure I will not need the keywords for anything other than searching.

Answer 1

I recommend using the following table setup:

STUFF_TYPE_CODE table

• STUFF_TYPE_CODE, pk
• STUFF_TYPE_DESCRIPTION --IE: users, groups, pages, etc...

STUFF_KEYWORD_XREF table

• STUFF_ID, pk
• KEYWORD_ID, pk
• STUFF_TYPE_CODE, pk, fk

KEYWORDS table:

• KEYWORD_ID, pk
• KEYWORD_DESCRIPTION

This setup will let you:

1. store the keyword value once
2. save multiple associations with that keyword to various things
3. a keyword can exist without any associations

All three columns in the STUFF_KEYWORD_XREF table need to be the primary key to ensure that there aren't duplicates based on all three values.