If this site is going to be used for ad-hoc reporting, and you anticipate a large number of rows, you should design the database as a Data Warehouse. This shifts the focus from normalization to performance and query efficiency, which sounds appropriate for the application you've described.
To learn more about this, look into Dimensional Modeling. Those tables with large numbers of columns representing numeric data would most likely be "Fact" tables and the smaller, more descriptive tables would be "Dimension" tables.
Ralph Kimball has published lots and lots of good information about designing, implementing, and maintaining data warehouses. Read his stuff!
According to MySQL, AES encryption (Advanced Encryption Standard) is the best method available for providing reversible encryption and decryption in SQL.
Formerly known as Rijndael, the AES_ENCRYPT and AES_DECRYPT functions are now built-in to MySQL so you can take user data, encrypt it with a salt, store it in your database, then extract it again later and decrypt it.
Define your salt
You'll need to apply a salt to the data that you encrypt. This is a special code that the encryption algorithm uses which works a bit like a key.
You'll need to provide the exact same key back to decrypt the data, and if an attacker should gain access to your database, they won't be able to decipher it without knowing the salt.
If you define your salt in PHP like this, you'll be able to pull the constant into your SQL statements more easily.
if(!define('SALT')) define('SALT','897sdn9j98u98jk');
To insert data into your MySQL database and encrypt the sensitive information, you'll need to issue a command like this, along with your salt.
INSERT INTO your_table (username,email,shoe size) VALUES ('$username', AES_ENCRYPT('$email','".SALT."'), AES_ENCRYPT('$shoesize','".SALT."'));
This will insert the username in plain text, as it's non-sensitive, but encrypt the user's email and shoesize, to prevent them from being viewed without access to the salt.
At some point, you're going to need to access some of the data you stored in its encrypted form, and you can do this very easily using the AES_DECRYPT function of MySQL and the same salt you used when you encrypted the data and inserted it.
SELECT username, AES_DECRYPT('email','".SALT."') AS email,
AES_DECRYPT('shoesize','".SALT."') AS shoesize FROM your_table WHERE username ='fred';
If you SELECT the encrypted data without running it through AES_DECRYPT or with the wrong or no salt, you'll get an ugly, unreadable string of odd characters. This means if an attacker manages to access your database, but does not have access to your server to view the salt, they won't be able to read any of the data you've stored. At least, not without going to great lengths to try and decrypt the data.
Updating encrypted records is very similar to insertion. Basically, you just apply the same salt and re-issue the AES_ENCRYPT command to re-encrypt the data again and lock it away safely.
UPDATE your_table SET email = AES_ENCRYPT('$email','".SALT."'), shoesize = AES_ENCRYPT('$shoesize','".SALT."') WHERE username= 'fred';
Searching encrypted data using both AES_ENCRYPT and AES_DECRYPT
Things get a little bit more complicated when you need to search for data that's encrypted and then display it in its unencrypted form.
Say you wanted to search for a user using their email address, but you'd encrypted that in the database. First, you'd need to encrypt the email address you want to search for with AES_ENCRYPT and your salt, and then you'd need to use AES_DECRYPT to ensure that MySQL decrypted it, returning it in a readable format.
You can achieve this, using code a bit like this:
SELECT user_username,
AES_DECRYPT(email,'".SALT."') AS email,
AES_DECRYPT(shoesize,'".SALT."') AS shoesize
FROM your_table WHERE
(email = AES_ENCRYPT('$q','".SALT."'));
For further informations, please see this link: http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html
Best Answer
I worked on a system that had a list of privileges (IE: Create users, modify customer accounts, etc) that were then assigned to groups. Users were then assigned to those groups and then had all the privileges of the group.
In our application when we wanted to check a permission we had a method
DoesUserHavePermission(PermissionEnum.Option)that we could call (and later turned into an MVC controller action filter). The method looked at the user to the group to the permission. This allows us to quickly add a permission to a group of users. IE: All the sales people now need the Add Customer permission, just add it to the sales group and every one gets it.We had 5 main tables for our permissions: Users, Groups, Privileges, UserGroup, and GroupPrivilege. The logic was that if there was a privilege that a group had there was an entry in the GroupPrivilege table (eliminated the need for bit fields) and if a user was part of a group there was an entry in the UserGroup table. (GroupPrivilege and UserGroup are many to many union tables).
This has kept the configuration down to a minimum after initial setup and if we need a group of users with a different set of permissions we can create a new group with just the privileges needed and assign the users to that group.