Let’s talk Database structure | MDS

So currently, in its infancy, MDS has a primitive database structure which is quickly expanding everyday with every new feature and need. Previous solutions I’ve seen relied on mutliple databases, and in the end we may need to look into that as well, and I’ll get into why in a moment. But first, let’s just look at what the current structure is.

Presently we have only four Database Tables to get the application running. Remember, MDS and SDS is only a shell, so it’s initial needs are very limited until plugins come into play:

  • Users
  • AIOX
  • Roles
  • Role_Assignments

Table “AIOX”

Now the only one that really stands out as weird is the AIOX. I’m currently using that as a version tracker for the Database scheme where it only has 1 Field (Version). As upgrades start going forth, and I add or remove functionality, I need a way to make sure that the application can still recognize and communicate with the application. For now, I’m using the AIOX table to hold the database’s Version, which will then be cross checked by the application’s AIOX_DB library version. If they don’t match, then upgrades need to go forth, and if they do, then the application is okay to continue.

Table Users

At the simplest form, users has the following fields

  • ID
  • Name
  • Salt
  • Password
  • Active

The Salt is generated randomly as any hash would, and is used in combination with the end users desired Password to create a hashed password. Using a combination of simple SHA512, the password is secure enough for our purposes.

The Active field is a simple Integer, since SQLite doesn’t have a Boolean Datatype, we use Integer and leave the logic to the application to figure out. This will make migrating from SQLite to SQLServer or MSAccess simpler in the future if that’s ever desired.

Table “Roles”

Roles is merely a list of a role name and description. The actual scheme is as follows:

  • ID
  • Name
  • Description

The idea is, as we develop, instead of using User/Group for authentication, we have use Role-Based ACL to determine if a user can do/view something. So we create a set of roles ahead of time “Can_View_Users” and give it a description “Allows a user to view other users” and then the application runs a validation of

If roles("Can_View_Users") Then
'Load All Users
'Notify Insufficient Rights
end if

Of course there has to also be a way for us to assign roles to people so…

Table “Role_Assignments”

  • ID
  • RoleID
  • UserID
  • AddedByID
  • AddedDate

So, we take the UserID of the person who is SETTING the Roles and place it into the ‘AddedByID‘, Adding the affected User’s UserID into ‘UserID‘, and place in the ID of the role we’re assigning into ‘RoleID‘. Roles are then grabbed at launch and during refreshes, stored into a Dictionary/Array, and then go from there.


In the future, there’ll be external databases coming into affect later. For instance, the majority of the theme I’m working with is Army related, due to the fact I’m an Army Vet. So for testing, all my hard coding is entering in Army Ranks, Lingo, etc. Later though, there’s gotta be a way to change it to make the application neutral. So much like Language Files are created for different languages to be used in applications, Military Databases will be created to reflect the different Military themes. Marines for instance have very similar ranks, but there’s no PV2 and SSG (E6 Army) is actually a SSgt (E6 Marines) or a TSgt (E6 Air Force) ((Fun Fact: SSgt in the Air Force is a E5….weird)).


Plugins also have a role to play in the Database field. Starting today (4/19/2015), Plugins now have an “Activate” subroutine which checks to see if needed databases and resources are installed, and if not, creates them. So for instance, since I have my HRPlugin being created, I needed to create a few new tables:

  • People
  • People_Types
  • Units
  • Units_Types

A lot of fun.

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.