Joining in order to Get AccessibleModules

[jwcoleman87]

So I have these tables:


What I want to do is find out what modules a specific technician has access to based on their job function(listed as JobTitle)

I think I'm off to a good start here:

            Dim UserJobs = From user In db.TTUsers _
                               Join job In db.TTUserJobs _
                               On user.TTUserID Equals job.UserID

This code should get the jobs for the user. Now I need to go one step further and get the modules accessible by each job for that user.

Can anyone shed some light on this? Many examples are either oversimplified by the msdn or overcomplicated by someone doing much more complicated things.

 

[jwcoleman87]

            Dim ModuleAccess = From user In db.TTUsers _
                               Join job In db.TTUserJobs _
                               On user.TTUserID Equals job.UserID
                               Where job.UserID = "Jonathan.Coleman"

            For Each row In ModuleAccess
                MsgBox(row.job.JobTitle)
            Next

Correction, with that where clause, I can get all the jobs for a specific user. Unfortunately this does not cover module access as intended, since I have only bridged the gap about halfway toward the table I need.

 

[Herman]

The main problem you are going to have here is one of structure. Think long and hard at how you need to split your data into multiple tables. The tables and fields you have right now are not very uniform or useful as they are. That is entirely up to you and depends only on your needs, but if it was me I would do something like this instead:



This way, table names are uniform, primary and foreign keys are easy to see and uniform, and you have relationships that actually do something.

Improperly planning your DB structure or rushing it is the worst thing you can do, it is shooting yourself in the foot for the future.

 

[jwcoleman87]

            Dim ModuleAccess = From user In db.TTUsers _
                               Join job In db.TTUserJobs _
                               On user.TTUserID Equals job.UserID _
                               Join modules In db.TTModuleAccesses _
                               On job.JobTitle Equals modules.JobTitle
                               Where job.UserID = "Jonathan.Coleman" And job.JobTitle = modules.JobTitle
            For Each row In ModuleAccess
                MsgBox(row.modules.ModuleAccess)
            Next

This gets me the information that I want!

I will look into your structure here, and hopefully learn a little. Could you explain your naming conventions for me? So that I can better understand the logic behind your tables.

 

[jwcoleman87]

I've been able to mirror what you're doing in my project, although all my arrows point the other way.
View attachment 4126

My foreign keys are in the userjob and jobmodule table, as there may be many entries of jobID or ModuleID, as a user may have more than one job and a job may use more than one module. I think this makes sense.

 

[jwcoleman87]

Another question, would you enlighten me on what kind of data type you're using for your primary keys? is it an autogenerated number?

 

[jwcoleman87]

Also, another unfortunate thing is that the join syntax is escaping me for this join, help would be appreciated.

 

[jwcoleman87]

This should do me, although I'd like to learn how to optimize this this much more than it is!

            Dim AccessibleModules = From user In db.TTUsers _
                                    Join userjobs In db.TTUserJobs _
                                    On user.TTUserID Equals userjobs.UserID _
                                    Join jobs In db.TTJobs _
                                    On userjobs.JobID Equals jobs.JobID
                                    Join jobmodules In db.TTJobModules _
                                    On jobs.JobTitle Equals jobmodules.JobTitle
                                    Join modules In db.TTModules _
                                    On jobmodules.ModuleID Equals modules.ModuleID

            For Each row In AccessibleModules
                MsgBox(row.modules.ModuleName)
            Next

I think these are the correct conditionals to get only the modules accessible for a specific user:

            Dim AccessibleModules = From user In db.TTUsers _
                                    Join userjobs In db.TTUserJobs _
                                    On user.TTUserID Equals userjobs.UserID _
                                    Join jobs In db.TTJobs _
                                    On userjobs.JobID Equals jobs.JobID
                                    Join jobmodules In db.TTJobModules _
                                    On jobs.JobTitle Equals jobmodules.JobTitle
                                    Join modules In db.TTModules _
                                    On jobmodules.ModuleID Equals modules.ModuleID
                                    Where user.TTUserID = "Jonathan.Coleman" _
                                    AndAlso jobmodules.JobTitle = jobs.JobTitle _
                                    AndAlso modules.ModuleID = jobmodules.ModuleID
            For Each row In AccessibleModules
                MsgBox(row.modules.ModuleID)
            Next

 

[Herman]

I will look into your structure here, and hopefully learn a little. Could you explain your naming conventions for me? So that I can better understand the logic behind your tables.

Another question, would you enlighten me on what kind of data type you're using for your primary keys? is it an autogenerated number?

I use the In prefix for primary keys, the Ex prefix for foreign keys, and the Id suffix for keys in general. Primary keys are auto generated and unique integers.

Also, another unfortunate thing is that the join syntax is escaping me for this join, help would be appreciated.

Writing Linq is no different than writing SQL, the words are a little different but the rest is the same. So start from the table where the field you need is, join the relationship tables you need for all your conditions and selected fields, and specify which fields you want.

Dim AccessibleModules = From m In db.TTModules
                        Join jm In db.TTJobModules On jm.ModuleID Equals m.ModuleID 
                        Join uj In db.TTUserJobs On uj.JobID Equals jm.JobID
                        Join u In db.TTUsers On u.TTUserID Equals uj.JobID
                        Where u.TTUserName = "Jonathan.Coleman" 
                        Select m.ModuleID

Use the primary keys and foreign keys only for joins ideally, since these are part of the clustered index in the database, which makes searches much faster. If you need to use another field, make sure to create the appropriate index in the database.

If you need to select multiple fields the easiest way is through anonymous types:

Dim AccessibleModules = From m In db.TTModules
                        Join jm In db.TTJobModules On jm.ModuleID Equals m.ModuleID
                        Join uj In db.TTUserJobs On uj.JobID Equals jm.JobID
                        Join u In db.TTUsers On u.TTUserID Equals uj.JobID
                        Where u.TTUserName = "Jonathan.Coleman"
                        Select New With {.ModuleID = m.ModuleID,
                                         .UserID = u.TTUserID}
MessageBox.Show(AccessibleModules.First.UserID & " | " & AccessibleModules.First.ModuleID)

Also it looks like you started to adapt your schema, but you havent finished. Make sure you do NOT use the user name as a primary key, assign it an autogenerated number instead.

 

[jwcoleman87]

This is great, things are really starting to come together for me in this project I've been working on! Things are coming together in so much as I have a good feel for where I'm going and I'm using the right technologies to get the job done. The reason for this module access for users is that there are multiple database applications rolled into one nice package. Production leads need to see more modules than the technicians, and testers, and inventory team etc.. Using this query I can delimit the modules each user will see when they log in based upon their job function. I work at a company where we have about 25 technicians repairing over 200 laptops per day. We do warranty repair and I'm just a humble lead trying to develop a way to keep better track (real time tracking) of where our product is as well as who worked on it and did what with it. Real time is necessary because the leads need to make quick decisions to expedite repairs or re assign them to different technicians. The management team also needs metrics as far as the dispositions of fresh units. I am hoping to meet all of these needs with this application I'm developing. I'm just a noob programmer with a passion for making useful stuff.

My next task is to set up tables for keeping a history of repairs, instead of keeping a daily snapshot. I also need to separate my module code from interface junk. I've been considering this single responsibility principle and I'm brainstorming a way to have one form which handles the interfacing with all of the modules. kind of like a home page with access provided to other pages where you do module specific stuff like track repairs, log final testing, modify an asset recovery inventory, etc....