Stephen Moretti (cfmaster) wrote: >Ian Vaughan wrote: > > > >>How could I display the correct menu options which are generated from the >>correct cfquery based on the user logged in. >> >>So if the user logged in is from the personnel department the following query >>populates the select boxes, but if the user logged in is from the finance >>department then the finance query runs on the page to populate the select box. >> >>Can anybody see any flaws with this approach ? Or how it can be improved to >>make the solution more flexible? >> >> >> >> >Ian, > >Are you saying that you have a separate category table for each department? > >>Yes, each department about 6 in total > Ok - you definately need to abstract the design of your database. At the minute, if you need to add a new department or remove a department you have to get your hands dirty, create a new table or remove a table from the database and make changes to the code to allow for this.
What you need to do is have one category table, one user/member of staff table and a department table. The departments table maintains the complete list of departments along with a unique ID for each department. The same is true for the users and categories tables. You then need to relate these tables to one another, ie. have tables that tell you which users belong in which department and which departments have access to which categories. Take a look at the ER diagram I've run up for you here http://snipurl.com/bilm It fairly simplified, but hopefully it should help to make sense of what I'm describing above. With a database structure like this you can add and remove departments extremely easily, add new users to departments and change the categories available to any given department as well as allow multiple departments access to one category. How does this work? Your user logs in, so you get a unique identifier for them by querying the users table during the login process. SELECT UserID FROM Users WHERE UserName='#form.loginname#' AND Password = '#hash(form.loginpassword)#' With that UserID you can identify the department(s) that that user is a member of, by querying the link table between user and department. SELECT Department.DepartmentID, Department.DepartmentName FROM Department INNER JOIN DeptMembers AS DM ON Department.DepartmentID = DM.DepartmentID WHERE DM.UserID = #qryUser.UserID# If you want to know what categories they can access you can get this information by JOINing DepartmentMembers to Categories through DepartmentCategories. SELECT Categories.CategoryID, Categories.CategoryName FROM Categories INNER JOIN DepartmentCategories AS DC ON Categories.CategoryID = DC.CategoryID INNER JOIN DepartmentMembers AS DM ON DC.DeparmentID = DM.DeparmentID WHERE DM.UserID = #qryUser.UserID# This doesn't take in to account handling your tree of categories, but as you've more than likely got the code to manage this, it shouldn't be too hard to integrate what is here with what you've got for the category tree. BTW : You should have a look at John Celko's nested set model (http://snipurl.com/JoeCelko_NestedSets - you will need to register on the site, but its free and I've not had any spam from them) Hope this helps Regards Stephen ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188514 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
