'Users' SQL table out of control. Searching for a better way

I've been programming and using a survey software for more than three years. It began as a simple survey system, with a basic 'users' SQL table, as you can find, for instance, in most CMS's. I initially planned to use the program two or three times, with 30 users or so for each survey.

Today, the program has grown and is used for one or two sur开发者_高级运维veys every month. A survey may have hundreds of users, which makes the table awfully long and confusing.

I can't find a best way than a classic user list, though. I though of creating a new table for each survey, but I feel it's asking for problems. What I do now is to archive the complete SQL database when a survey is finished, which is convenient enough for now but will turn into a nightmare when I'll have to quickly find the results of an old survey.

I'm confident I'm not the first one to encounter this problem and would be happy to know how you solved it. Are there some kind of best practices for it?

Thanks!

---

One solution is to maintain two tables. Users and Users_History.

Once a survey is complete, you can move the data to users_history. That way, DB that affects app performance would stay within a manageable size. For analysis purpose, data would always be available in history table.

---

If the set of users for each survey is distinct (the same person in two surveys would have two different user accounts), you could add a column to users referencing the surveys table.

ALTER TABLE users ADD COLUMN survey INT NOT NULL;
ALTER TABLE users ADD FOREIGN KEY (survey) 
    REFERENCES surveys (id) 
    ON DELETE CASCADE;

If you want to allow users that aren't associated with a survey, allow the survey reference to be NULL and set the foreign key to ON DELETE SET NULL.

When it comes time for you to operate on the users table, use the column to get only the users that are a part of the survey of interest.