Looking for your opion

[eluser]megabyte[/eluser]
In a nutshell I'm designing a custom cms. Its designed where there are 2 major permissions.

A company admin, and a site admin

The company Admin has access to all sites

The site admin has access to 1 to (n) number of sites

right now I have it so they select checkboxes for each site they are given rights to, then I keep this list as a comma deliminated string under their profile in the db table.

Should I be keeping it in its own table as individual entries instead?

ie.

table

userid site

1 2
1 4
1 4
1 6
1 8

like that instead.

I realize there ae many mways to solve a problem.

Has anyone ever tackled this before? If so, how did you structure your db, and how did you structure your form.

[eluser]bugboy[/eluser]
the way i would do it how you suggested in a separate file.

i'd have the user table, a site table and a link table.

so

user
pk = user_id

site
pk = site_id

user_site
fk = user_id
fk = site_id

this will allow for easier maintenance and will also make things a little faster by getting SQL to do all the work and not php.

just my two pennies worth there

[eluser]megabyte[/eluser]
I agree with you as well. The only design problem I am having is how to incorporate the sites that are selected into a form.

The only way I can think of is keeping the site admin permissions seperate from the form that adds a new user.

ex.

- you add a new user

- then you have to go to a screen where you set the sites allowed to view.

[eluser]bugboy[/eluser]
couldn't you have it so lists all the sites as checkbox's and tick the check box's that are needed?

so

you pull the sites from site

So when the forms submitted you'd add the data for that user, collect that users id then loop through each check box in the array and then add it to user_sites.

Code:

//model
function addUser($data)
{
$this->db->insert('user', $data);
return $this->db->insert_id();
}


//model
function insertSites($key_one, $key_two)
{
$data = array(
'user_id' => $key_one,
'site_id' => $key_two
);
$this->db->insert($this->_page_global, $data);
}

//in controller
$insert = $this->users->addUser($insert);

// in controller
foreach($_POST['multi_site'] as $key => $value){
$this->sites->insertSites($insert, $value);
}

[eluser]megabyte[/eluser]
I could, and thought about that. The problem I couldnt solve, is how do delete them with the checkboxes from this same form later on.

Adding with checkboxes is easy, its the updating that worries me.

Say for example I go back, and want to remove permissions for a few sites and add a few more at the same time. I would have to check which ones are already checked, and then only add ones that arenot already in the db table.

[eluser]bugboy[/eluser]
the way i do it is like this

Upon the edit, list the sites checking the ones that are already checked

Do your edit and what not.

Then submit the form after the edit and the checkbox's.

Once the information has been edited, delete all the references in use in 'user_site' to that user and the loop through the checkbox post array again.

This way you don't risk having duplicated data as you deleted it first then re submitted the data.

So in effect wiping and then resubmitting.

It maybe the long way round but it works a treat

I'd also use two forms one for adding the user and one for editing the user. Then you can control what's needed and whats not.

[eluser]megabyte[/eluser]
Thanks bugboy. I figured thats what you were gonna say. I was just hoping there was some cool super hero way of doing it that I couldnt think of that would make the least amount of changes to the db table.

[eluser]bugboy[/eluser]
yeah thats what i thought when i was doing something similar.

There maybe i just haven't found it yet.