14 Jan 2025 - rich
Adding INNER and LEFT joins along with aggregate tables for many-to-many relationships has significantly enhanced the functionality of what was this simple database. It is not possible to add roles, tagging, notes to companies, recruiters, job postings, and the actual listings. This will also allow for the addition of status tracking and calendar and event scheduling.
There is still much to do here, and even more to clean up and improve, but the strides made are quite significant and have moved this from toy to very useful tool.
Even more importantly, the fixes to the database logic and the introduction of these new tables and relationships scale the tool well beyond this one application and
can and will not be used in other database implementations.
I will see how all this pans out when I move the sqlite database over to postgres and mysql. Many of the routines and functions should be able to move over from one to the another with very little (if any) modification.
it is now possible to create a note independently and then subsequently attach it to one or several objects. The initial implementation will allow notes for people, addresses (e.g. location and code key info) and events.
similar to ROLES, tags may be attached to most items. Whereas ROLES has a stringent requirement that downstream tables need to be updated if a role is removed, this is not the case for tagging as it is acceptable to have undeclared tags (as a result of removing a tag but not the references to them) as the addition or creation of that tag will simply result in a table update and things should continue to work as advertised.
Roles are predefined and are used to track the function of people, or other required objects. As mentioned, unlike Tags, Roles can be added as this will simply update the options to be displayed, but removal of the Roles will require removal of the reference to the tags. This is required more for the level of importance but also will prevent Role drift…