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

Question

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 surveys 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!

MySQL has no problem with tables with millions of rows. You can add 30 users twice a month well beyond this millennium. — Andomar, Mar 20 '11 at 17:55
Yes, I know that there is no limit. The limit, in my case, is to manage users myself. Some concrete cases: Two users have the same jsmith username; A user has been invited more than to one survey with a two-years interval. Keeping track of them is hard; I'm a bit paranoid about a user being able to access another user's survey so I'd like to have something like sub-tables. It's very possible that I just have to live with growing tables and rework the logic -- and that's what I'm about to do -- but I wanted to know if there was another way. — Stephane, Mar 20 '11 at 17:58
If one user can see another user's survey then you have a much bigger problem than your imaginary 'too long table' problem. Ask another question about how to make unique user identifiers. — Clodoaldo Neto, Mar 20 '11 at 21:13
Of course they can't! As I said, this part is just plain paranoia, if something did go terribly wrong. — Stephane, Mar 20 '11 at 21:36

score 2 · Answer 1 · answered Mar 20 '11 at 18:00

2

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.

answered Mar 20 '11 at 18:00

Shamit Verma

3,839
23
22

Yes I agree Shamit, partitioning the table into "live" and "archive" is a great idea. How you partition archive the depends on volume of data, access activity, etc. We did yearly archive tables on our transaction systems. We also built indexed views across the live and archive tables in our reporting system to enable consolidated roll-ups. – stephbu Mar 20 '11 at 18:29

score 1 · Answer 2 · answered Mar 20 '11 at 18:19

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.

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

2 Answers2