CODEWITHSUNDEEP
sqlmysqldatabase-designdata-modeling
Mark
Mark

Reputation: 5653

MySQL - keyword search table - a good idea?

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, and 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 else than searching.

Upvotes: 1

Views: 867

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332571

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.

Upvotes: 3

Related Questions