This tells me a lot about your project. Glad to hear you are learning PHP and SQL, etc. Nice to hear you are interested in those…
So, you have a user’s table set up already in a database. Oh, wait… Are you using SQL, MySQLi or PDO? I suggest learning PDO because it is much more secure than the others. And, it is extremely handy when doing multiple updates as you prepare the query ahead of time and then just fill in the data. Here is a great link to a PDO tutorial that tells you everything you need. ( Also, most professional programmers are using PDO now. ) PDO Tutorial
Now, back to the layout. You have a USERS table with information of 100 users which may become many more. In that table, you should have a field with one or two possible layouts. Since any one user can access different departments, I feel it would be best not to use a canned user-level system. Normally, for this type of system, you would assign a user-level and then have another table that listed the user-level and all of the departments they can access. This works for most layouts. But, since you need to pick and choose which department a user can see, it would make the user-level table complicated. I think just keeping an array of the departments that any one user can view would be easier to handle.
Therefore, in the users table, you would need to add a field to hold the array. This field could be very large if the user is allowed to view large numbers of departments. The array itself would need to hold the store-id department-id. Sometimes when you develop a database layout, you must think from the user’s standpoint. If I was a regional manager, I would like to be able to select a store, then a department to view. Also, I would expect to be able to see reports based on any one store with all of the department totals. To do that, you would need an array that includes a list of all stores I would be allowed to visit and all departments inside each store. If I was a local manager of a huge store, I might only have half or a quarter of all the departments under my direct control. (Some stores have several managers with a general manage above all of them.) Therefore, my array would only have my one store in my list, but, a number of departments listed under that one store entry. Does all this make sense to you so far?
Now, in an array, you can have a multidimensional array with the main index called, let’s say, “stores”. Under that index you would have all of the stores that this user can view. Then, under each store, you would have another array that would list all of the departments this user can view inside this one store. So, to access it you could do a simple lookup like: $depts = $stores[$current_store][$current_dept]…
This is one possible way to handle this.
Another way, which is what a lot of professional programmers would suggest is to create a table of access lists. The advantages to this way is to be able to do super fast queries to acquire the lists when needed. To do this second way, you would create a simple table and call it something like “permissions” or “access” or… The structure for this would be simple, just these items:
id (for indexing)
user_id (to point back to the user)
store_id (to point to the stores table)
depart_id (to point to the departments table)
All entries would be INT(11) format and easy to set up. Handling it this way, you can build very small queries to access any one user’s access-levels. Just SELECT * FROM permissions WHERE user_id=… This would get all the info in one small query for any one user. Then, of course, you can alter the query to get just what you need for displaying like SELECT store_id FROM permissions WHERE user_id=… This would get all the stores that any one user is allowed to visit. And then a simple FOREACH would let you add them into a drop-down for selection of stores. And, once a user has selected a store, you could run a quick AJAX call to load a second drop-down with SELECT depart_id FROM permissions WHERE user_id=… AND store_id=… And, that would get the data to load into the second department drop-down.
I think for your use the second version would be easier to work with and actually more professional. If you were working with less users, stores and departs, the array might work easier, but, with these amounts the real database layout is better. ( And, easier when viewing data, too. )
Well, there are two ways to handle this. Gives you a lot to think about. Good luck with your design!
Ernei
