Almost every web application and system deals with data in some regard. yougra.ph relies extensively on data collection and visualisation and as a result, I spent some time working out how the data should best be stored on my server.

 

Tools

  • MySQL
  • MD5 Hashing
  • openSSL two way encryption
  • Hashids

 

I designed and set up a MySQL database with a set of tables which would allow me to store all the data the system would create and retrieve through the messaging server and web application.

The structure:

dbstructure

 

Three tables are used for the data the system requires.


The first table ‘users’ stores a unique user_id, the number they used to register, the date their account was created and a securely hashed password.

The password is hashed using the MD5 hash as one of the most basic security considerations within the system. Hashing manipulates the password using an algorithm which changes it from plain text to an obfuscated string of characters. This cannot be reversed. Instead, when comparing passwords in parts of the system (eg. logging in), the input is run through the same algorithm and checks for a match. This avoids having to store passwords in plain text in a database in case of a common password among services and prying eyes of web application administrators.

real data stored in the users table:

Screen Shot 2016-04-26 at 17.09.51


 

The second tables hosts week timeframes. User testing and conversations with support professionals established that one week is the ideal timeframe to give a snapshot of mental wellbeing. As a result, the day graphs are clustered in week timeframes allowing for reference use and pattern recognition in support scenarios as well as sharing with close friends and family.

One of my users suggested that these weeks could be generated as links with hash_ids – similar to how youtube, instagram and similar websites create URLs. Each page would have no indication of from whom the data is representing. This means, that when shared, the link would only take on meaning when surrounded with related discussion. There is no context of ownership without further discussion.

Generating these week_ids and associated urls was managed using a PHP library called Hashids.  Once installed, Hashids takes two integers and a salt and generates a unique string of characters which is then used as an week_id and corresponding directory on the server. There is no way to randomly stumble across a directory using unique hash ids.

real data stored in the weeks table:

Screen Shot 2016-04-26 at 17.10.29


 

The final table is entries. Entries holds all the information about the messages which have been sent to the text messaging service.  This includes a unique index id, time sent, associated emotional levels (on a scale of 0-100) and the body of the text message. This text message body is stored after being encrypted using OpenSSL and a secret key. OpenSSL allows for two way encryption, meaning it can be hashed and then unhashed. This means that when the text message is stored in the database, it is scrambled so that any prying eyes cannot see the user’s logged thoughts. I made a point of the importance of this encryption early on in the project at Gurus’ Day and felt it would have to be present even in a final prototype.

real data stored in the entries table:

Screen Shot 2016-04-26 at 16.56.18

 

 

With the database established I could move on to developing the messaging service and web application.