MySQL architecture for different word clouds

Question

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.

• Rick James: Is there one "anonymous", many users, and many organizations?
• Dennis: @RickJames yes exactly true!
• Rick James: 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.)
• Dennis: Maybe I wasn't clear enough. I want to create word clouds for both as well!

Answer 1

CREATE TABLE words (
 user_type ENUM('anonymous', 'users', 'organizations') NOT NULL,
 group_id SMALLINT UNSIGNED 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)
) ENGINE=InnoDB;

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.