I want to create a word cloud on my website based on the keywords people search.

I have one free text input field where something can be entered. I have 3 different user types on my website (anonymous, users, organizations). And I want to keep create a wordcloud on the most searched keywords for all these 3 types separately.

I was wondering what is the best architecture for MySQL to accomodate this problem.

Is it better to save all user types in 1 table and keep a "user_type" field? Or is it better to create a different table for each user type (for performance reasons?)?

As well, longer term-wise, I would like to use the user's searching history to provide better results for him. So to avoid rework later, I think it's good that I already have it linked to a specific user_id already right now.

Can anyone suggest me a mysql architecture for this issue? I am thinking of going the way as suggested by user @Derek Downey in this post. With adding a user_id in the DateofSearchTerm table.

  • Is there one "anonymous", many users, and many organizations?
    – Rick James
    Jun 2, 2018 at 3:20
  • @RickJames yes exactly true!
    – Dennis
    Jun 3, 2018 at 10:34
  • Do you need only clouds for each user? Or do you also need a cloud that includes everyone's words? Or both? (It is hard to design a schema without knowing what the queries will be.)
    – Rick James
    Jun 3, 2018 at 13:52
  • Maybe I wasn't clear enough. I want to create word clouds for both as well!
    – Dennis
    Jun 3, 2018 at 14:52

1 Answer 1

    user_type ENUM('anonymous', 'users', 'organizations') NOT NULL,
    word VARCHAR(...) NOT NULL,
    ct MEDIUM INT UNSIGNED NOT NULL,   -- how often user used the word
    PRIMARY KEY(user_type, user_id, word)

This will let you build an overall cloud, or a cloud for an individual user/organization.

SELECT word, SUM(ct) FROM words;  -- overall

SELECT word, ct
    FROM words
    WHERE user_type = 'organization'
      AND group_id = 123   -- for a particular user

You have not really mentioned a requirement any date.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.