I'm wondering the best performance solution to this programming challenge.
Here are the scenarios
Customer 1 purchases the mailing list, classifieds, and faq module.
Customer 2 purchases the mailing list.
Customer 3 purchaes the faq module and the enhanced stats.
I need to dynamically generate a control panel for each customer based on the packages they have purchased.
I have 3 tables to support the variable permissions and to build the menu.
Modules are housed in subdirectories.
1.) cp_mods
this table contains the paths to the modules, their id, and their name
mod_id
mod_name
mod_path
class_id
2.) cp_mod_classes
this table contains classes of modules, one class may be for all functions relating to mailing lists
class_id
class_name
3.) cp_perms
this table contains user permissions, if a record exists
user_id
mod_id
4.) cp_users
this table contains users
user_id
user_name
user_password
mod_ids
Option 1 - User an index table and join to select the modules
SELECT * FROM cp_mods INNER JOIN cp_mod_classes ON cp_mods.class_id AND cp_mod_classes.class_id AND cp_perms ON cp_mods.mod_id = cp_perms.mod_id WHERE cp_perms.user_id= $user_id
Option 2 - serialize the array with the mod_ids the user is granted permission for, then store that data in the user's table, select the data, unserialize it, implode it, then select the appropriate modules
SELECT * FROM cp_users WHERE user_id = $user_id
$mod_ids = unserialize($row['mod_ids'])
$mod_id_list = implode($mod_ids,',')
SELECT * FROM cp_mods INNER JOIN cp_mod_classes ON cp_mods.class_id AND cp_mod_classes.class_id WHERE cp_mods.mod_id IN ($mod_id_list)
Which of these solutions is the best and why?
After writing this message it appears as though solution 1 is the best b/c it requires only 1 call to the db and would probably be much more efficient.
Are there any other ways to accomplish this that I'm missing?
