Help with DB layout

ChetUbetcha

I'm having problems with designing the layout of part of a database. Any help would be great. Tables: users & reports

Users can be admin, coordinator, team_lead or reviewer (admin_level 1-4)

After creating a report, I want to be able to assign users to be an admin, coordinator, team_lead or reviewer to any report. They can be assigned to multiple reports as well. I also need to be able to add or remove these users from their assigned reports.

What's the best way to design this database? Should I have tables for each admin_level? I have thought about adding a record to the reports table with their information each time they're assigned, etc. I keep making it halfway, but then run into issues.

What I've attempted so far...
Inserted a new record into reports table each time someone is assigned to it, with their respective admin_level, but then when I go to assign another user, I want to retrieve all from users table with respective admin_level and also display those that are selected with a checkbox. That would call for updating the records whether they were turned on/off and that's giving me headaches. Assigning the users with a checkbox array hasn't been an issue, but I need to research how to update the records if they are already selected and then turned off while going through the checkbox array after clicking Submit.

There are other things as well, but I've rambled on long enough. Thanks

laserlight

There is a many to many relationship between users and reports. As such, you need three tables: users, reports and users_reports (or whatever better name you come up with).

Now, if the role of admin, coordinator, team_lead or reviewer is specific to a report, you would add this column in the users_reports table.

ChetUbetcha

I can have the users and reports table, and create an assigned_users (or some other name) table.

Now, I assign a user to a report and have that report_id in the assigned_users table. I did this yesterday, and I was able to generate a checkbox list of users with the assigned people already checked. How do I get it so when I turn the checkboxes on or off, it inserts the new users assigned to the report and delete the users that were deselected?

Do I have to pass everything with arrays? My problem with submitting the information was when I had (using DW)

<insert type="checkbox" name="user_id[]" value="<?php echo $row_users['id'];?>">

It would only pass user_id into my insert loop, but not the username, email address, etc. It only passed that extra info from the last item in the list. I know that's because user_id is the only array, thus the only information. I'd like to have it so I can display their information and upload everything instead of only the user_id.

liquorvicar

In answer to your first query, there are a number of ways to do it. One would be to build an array of the current users assigned (from the database) and then to build an array of the new users assigned (from the form). You can compare the two arrays and delete any users that are in the first array and not the second array, you can add any users in the second array and not the first array.

As regards your second question, why do you want to pass more than the user ID from the form? I would just pass the ID as that's all you need to process the data for the lookup table (the assigned_users table).