Wizard’s Toolkit Documentation

Wizard’s Toolkit Database Conventions

There are 44 tables in the Wizard’s Toolkit database and all follow the same standards.

Table of Contents

Naming Conventions
Deleting and `DelDate`
wtkUsers Table

Naming Conventions

All data tables use the following standards:

  • All tables names start with 'wtk'
  • WordCaps for the column names
  • UID for their auto-incremented unique identifier field
  • AddDate for the date and timestamp of when the data was inserted

The Wizard’s Toolkit PHP code uses wtkInsertSpaces to automatically convert column names into user-readable values. For example it will change `FirstName` to “First Name” for both column headers on lists and for labels on forms. We use WordCaps but wtkInsertSpaces will do the same for snake_case.

All tables have both the auto-incrementing `UID` column and the `AddDate` column. `AddDate` is automatically filled with the current SQL date and time upon insert.

Deleting and `DelDate`

Many tables also have a `DelDate` column. Our philosophy is data should rarely be deleted. Instead if a user wants to delete some data which we need for historical analytics, we set the `DelDate` with the timestamp of when the request was made. This way the data is still available for analytic reports but can be hidden on regular listings. For example, if in your application you use `wtkUsers` for tracking employees and someone leaves the company when the employee is “deleted” it will actually `DelDate` them. Meaning it will set the `DelDate` column to the current date and time. This way the employee will not show in their list of employees but when they run historical reports it will still show what that past employee had done.

Of course the Wizard’s Toolkit login logic does not allow wtkUsers with a non-NULL `DelDate` to login.

For coding when you are creating a SELECT query you simply add this to the WHERE clause:

WHERE `DelDate` IS NULL

wtkUsers Table

Wizard’s Toolkit uses wtkUsers for managing users and their logins. User accounts use their email address for their login and to determine uniqueness. This is all managed in /wtk/wtkLogin.php.

Security Level data column

In the wtkUsers data table is the `SecurityLevel` column. This limits what web portals and/or which web pages a user can access. The back-office admin website that comes with Wizard’s Toolkit has a SecurityLevel of 90.

Many websites require three web portals. A back office where techs and developers manage users, review update logs when there are problems, review error logs, send out emails to users, etc. That is all provided with Wizard’s Toolkit.

Then you need to build the client portal which allows clients to do whatever their busisiness does. Sometimes the clients want to give limited access to their customers or vendors, and then you need to build a customer portal.

In this scenario you have everyone using the wtkUsers data table for logging in but set their `SecurityLevel` based on which portal they may log in to. For example:

Minimum Security Level RequiredWeb Portal
1
Customer Portal
25
Client Portal
90
Back Office

If a user tries to access a page with a higher Security Level requirement than their Security Level they are given a “restricted access” message.

Using this Security Level methodology makes it easy to add more levels and functionality. For example we could make 25 Security Level for regular client staff, and 30 for their managers, and 35 for their regional managers. Then in the client web portal staff would only be able to access pages based on their Security Level and within the PHP pages at the top all you have to do is add this code to set the Security Level at the top of a page (before wtk/wtkLogin.php).

$pgSecurityLevel = 90;

Note: a wtkUsers account with a higher SecurityLevel can always access lower-level pages. For example as a developer your SecurityLevel should be 99 and you would be able to login to the Customer Portal, Client Portal and Back Office.

Security Levels are managed via the wtkLookups data table which you can manage in the Back Office. Depending on your organization you may want to use a methodology like this:

wtkUsers.`SecurityLevel`User Type
1
Customers
25
Staff
30
Managers
95
Owner/CEO
99
Programmers

This way the owner of the company has access to the Back Office but you, as a developer, can restrict some pages so they do not accidentally send a Bulk Email to all users.

User-Specific Access

Some functionality is managed on a user-by-user basis. There are four data columns in wtkUsers table which determine whether a user is allowed to Print, Export, Edit Help, or Unlock pages. Their names are `CanPrint`, `CanExport`, `CanEditHelp` and `CanUnlock` respectively. These are ENUM with values of Y or N all defaulting to 'N' but of course you can change that in your database implementation.

These can be managed within the web portal to set on a user-by-user basis whether a user was allowed to see reports, export, edit help or unlock pages that are currently locked because a user is on the page and may have forgotten to logout.

Search results