How it works! Curious Room Database

Image 1: Relationship between tables: Curious Room 

How the project works:

There are many employees in a company and employees can sign up for an account. In this account ‘Curious Room’ the employee is entitled to five tokens a month. Each token allows an employee to have a half hour of free time to engage in a relaxing activity. Therefore each employee who uses the system can have two and a half hours to relax during work hours in any given month. Employees can only use up to two tokens per week so they have at least one session a week and on any week in the month they can choose to ‘spend’ their fifth token.

Employees can only use the system by choosing an avatar. The rationale behind this is that they are abstracting both themselves and the activity. Hopefully it will encourage new interactions within the company and allow a degree of chance whilst using the system. In other words there is less of a chance of two buddies pairing off each week for the same session. The idea is that one avatar will not know who they will be engaging in the session with.  Each meditation session lasts 17 minutes with 13 minutes to login and get to the session and a few minutes to give feedback at the end.   

Step 1: User logs in and has an avatar associated with their account.

Step 2: User can change their avatar to a new one for a session.

Step 3: User can request to meditate with another person.

Step 4: User makes a request to see if there is a)a person to engage in a session with and b) to see if there is a room free for the meditation session.

When this was first designed at the end of February it was more complicated but after further research two factors became an issue.

1.       That a session less than a half hour is just not realistic in real terms to relax

2.       That further down the project road, after testing matching people to different times might be an option.

For now the cleanest option to develop the project is to refresh the rooms every half hour and if an avatar requests a session with another if they are free then this is a match for a session. The user then will have approximately 3 minutes to go to a room to begin the meditation. Once the user commits to a meditation session their token will be used.

Token/Session Design Issue: 

Does the user table with the token get updated when both match (room & other avatar) or when the user reaches the room and places their token in the cradle? For this project it will run straight away and update on the database. For future when the person takes their token to the room and places the physical token (with an rfid reader tag inside) the cradle would then talk to the data base and update the database to reflect the token being subtracted from the users account.  

Image 2: Visual Representation of a physical token user would use to start session. Token would be embedded with RFID reader chip to communicate with database. 

The High View:

There are five tables in my database for my final year project. These tables are:

-          Avatar

-          Employee

-          Employee has session

-          Session

-          Room

The avatars table is a one to many relationship with employees so we have avatars for many employees. Employees then have many meditation sessions but linking a ‘many’ employees to a ‘many’ sessions table would be illegal in a mySql database, it is not possible. We have to break up this relationship and make it more manageable. This is done by making a ‘link’ table. So as you can see in the diagram one employee has many sessions and from the ‘Employee has session’ table we have a relationship – employee has many sessions with a session table (single). That sounds funny to clarify employee has many sessions. One session has many ‘employee has sessions’. 

The curious room table many sessions take place in one room at different times.

To look at the tables more closely the first table avatar has 3 rows:

Table Avatar

Id – (PK) unique, auto incrementing 1,2, 3 etc.

FileName, (associated with the jpg corresponding to the avatar that the employee will choose)

Name (ie name of avatar CalmStar)

Employee Table

(Fields right now just two users were inputted):

Employee ID (PK),   (right now just 1 & 2 employees have been submitted for this project.)

FirstName

SurName

UserName

Password,

Tokens

Email: fionakiely1@hotmail.com,

Avatar: 1,2

Last Token used on: date and time ( this is set to go back to reset on the 1^st of every month)

Employee_has_session table (both fields in this table make up the primary key so it is a composite key because an employee has to have a session for the event to occur)

Employee (FK, composite Key)

Session (FK, composite Key)

Room table (the room table has an id because if the system was developed there could be more than one room for different activities i.e meditation and yoga so each room could be identified individually.

Id (PK).

Number

Name of employee

Session Table

SessionID (PK)

Room

TimeStamp

Duration

Rating

This breakdown should give a clearer view of how the system works for users and for management of the database. In the next post I will talk about how this will move into Codeigniter as a full project.