[X]My Assistant

[monfort.brice]

Hello everyone,

I'm trying to design a database to keep track of employee certification.

The way I've imagined things so far is a table called tbl_certificate listing all the certificates the employee could be required to hold for a given position.
I also have a table listing all the existing positions in the company called tbl_Position and a join table called tbl_PositionCertificateJoin allowing for a many to many relationship between the two tables.

Now, where things get a bit trickier is that some certificates can have prerequisite certificates.
Each certificate can have: none, one or many prerequisites. And these prerequisite can chain (i.e. CertA requires CertB and CertC but CertD requires CertA and CertE).

I thought I could create a table tbl_CertificatePrerequisite having a ParentCertificate field and a Prerequisite field both linked to the CertificateID field of the tbl_Certificate.
However Access doesn't seem to like the idea of 2 distinct relationships between the same two tables.

Any idea on how to approach this concept? I guess I'm trying to make a self join many to many relationship, but I could not find resources on this particular subject so I'm guessing I'm going at it the wrong way...
Thanks for the input.
This post has been edited by monfort.brice: Dec 11 2017, 02:00 PM

[jleach]

Hello,

I don't think you'll be able to do this with relationships and SQL alone. Essentially you're talking about a hierarchy, which RDBMS systems don't handle well (Access less than others).

Structure-wise, it's fairly simple:

CODE

tblCertifications
... ID (autonum PK)
... CertName, etc

tblCertificationPrerequisites
... ID (autonum PK)
... TargetCertificationID (FK)
... PrerequisiteCertificationID (sortof-FK but probably not actually constrained)

(toss a unique index on the two FK-ish fields for good measure)

So, you have one table to hold all of the "children" and thus you create your link. By the nature of hierarchy, you re-calculate the "children" of each cert until you reach the bottom (or determine that you're in an infinite loop).

The difficult part of this is that you really have to use VBA and recursion to manage the hierarchy. If you're using a SQL backend, you can do some recursive CTE work, but it's still essentially procedure-work rather than "fully in the realm of tables and relationships" type of stuff.

hth

[monfort.brice]

Ok, so if I understand correctly, I'm on the right track with my tbl_CertificatePrerequisite table.
I just need not bother trying to create relationships to it.

Instead, when I want to check whether an employee hold a valid certificate and all prerequisite certificates I should have a SQL query lookup the top level certificates.
Then I call a VBA function checking that the employee also possesses valid children certificates for them. Calling that function recursively on the grandchildren and so on until I reach the bottom of the tree...

I think I see where I should go with this.

Also, thanks a lot, your use of the word hierarchy allowed me to find this article which covers the concept of complex tree and lookup functions.
It'll probably prove useful as I've never touch the subject before. Back to the drawing board I guess...

[theDBguy]

Hi momfort.brice,

Welcome to UtterAccess!


Thanks for sharing the link. It looks very interesting. Good luck with your project.

[jleach]

Sounds like the right path to me. Good luck: this is on the more difficult end of things to implement, but looks like you've got a decent handle on where to go.

Cheers,

[jleach]

Just to add a bit more: that link covers the topic in some depth. Maybe more than you need (and there's numerous ways to attack the problem, so keep that in mind as well).

I think the method you described would serve well. Get the top level credential, then look for all children recursively. Something like this aircode:

CODE

Function GetPrereqCerts(TopCertID As Long, ByRef Results As String)

  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("SELECT PrereqID FROM Prereqs WHERE ParentID = " & TopCertID, dbOpenSnapshot)

  While Not rs.EOF
    Results = Result & ";" & rs.PrereqID
    'recurse here for each child we find
    GetPrereqCerts(rs("PrereqID"), Results)
  Wend

  rs.Close
  Set rs = Nothing
End Function

By the time you're done walking the tree, the ByRef Results string is a delimited list of all cert IDs that are actually required.

The above doesn't worry about the order/nest levels of the certs, which makes things quite a bit easier. Otherwise you have to get quite a bit more creative with how you recurse things.

Call that function after obtaining your top level ID. You'll always have one more "entry point" function to kick off the recursive procedure, where you get things set up and ready to go. It would also be good to put a static counter in there with some high value. MAX_COUNT = 500 or something so you can error out if you hit 500 certs: enough so you'll never hit it in practice, but enough so that you can (more or less) elegantly error your way out of an infinite loop before you hit a stack overflow.

It's a rough draft, but between that and your decent logical handle on what to do, should give a good kick in the right direction. If you need a hand with anything else, stop back and ask.

Cheers,

[jleach]

Another note: one might be tempted to put some sort of calculated field into a query that uses this type of function to determine - on a "full recordset results basis", whether the given person is certified.

I'd advise against that. As you can see, for each resolution of "is certified", any number of recordset objects needs to be created (or any number of sub-queries ran, etc). Even in a "flat" structure, running queries that would do this for only one level of depth would be a massive performance issue.

If it is imperative that you get all certification results very quickly in realtime, I might recommend to consider breaking the rules slightly and storing the calculated value, along with some "after update" implementation to recalc (where such recalc would only happen for a single person at a time, and thus performance is still reasonable). That's a tough recommendation to make though: can never feel good about advising a less-than-best practice. So, if you really must have that and really want to do it correctly, perhaps consider moving to SQL Server (express editions are free), where you can utilize their more robust serverside processing and run real hierarchy CTEs in realitime without the massive performance hit.

[monfort.brice]

It would also be good to put a static counter in there with some high value. MAX_COUNT = 500 or something so you can error out if you hit 500 certs: enough so you'll never hit it in practice, but enough so that you can (more or less) elegantly error your way out of an infinite loop before you hit a stack overflow.

I am thinking of creating tbl_CertificatePrerequisite with the following structure :

CODE

tbl_CertificatePrerequisite
... CertificatePrerequisiteID - Int
... ParentCertificate - Int
... ChildrenCertificate - Int

and using CertificatePrerequisiteID as a node identifier passed to the recursive function in an array of traveled node. That way I can make sure I don't travel the same node twice without the need for a counter.

[EDIT]Spellproofing my post, I realized it would not really be a node identifier so much as a branch identifier, but I guess it works the same way... Walking up the same branch twice would end the recursive call.[/EDIT]

I might recommend to consider breaking the rules slightly and storing the calculated value, along with some "after update" implementation to recalc

That's a good idea, I could create a tbl_CertifedUntil linking employee to certain positions to avoid recalculating too often if I get performance issues...

Once again, thanks for the input.
This post has been edited by monfort.brice: Dec 11 2017, 03:51 PM