HealthAppDatabase
This page shows how to create your own database for an app rather than using an existing one. We are going to use the hospital health app you created in the basic tutorial and create our own hospital database for it.
To follow the steps, you must have already:
- done the basic tutorials
- done the setup in the Advanced Setup section
- installed SQL server and created a local database and account
- You'll need SQL Server 2008 R2 Standard, which you can get here. Login using your SITE credentials and follow the download instructions.
- completed the HealthApp project in the Basic Tutorial
- you can also get the hospital app from the repository though you should try to make your own as practice
- know basic SQL
Read the Database Naming Conventions to look at a sample database schema and to read all the rules for how the tables and columns in the database should be named and organized. You should definitely read that page, don't just keep reading here
We use a star schema for our databases. We have FACT tables which store the actual information. Each form in your app corresponds to a FACT table which stores the values for all the fields each time you submit. There are also LKUP tables which store the options in a drop-down selection control.
- Open Microsoft SQL Server Management Studio and connect to your local server (no seriously don't connect to the quickforms server).
- On the right-hand side you should see the Object Explorer. Right click on the Folder titled Database and chose New Database. In the dialog that opens type the name of the new database which in this case is 'hospital'.
- Download and open this file:SQLQuery2.sql. That is the sql script for creating all the tables for the sample app database. We will later change these tables for our needs.
- The first line of the script should be
USE [sample]. Change this toUSE [hospital]. If you create more databases, change it to the name of the database you created. Change "sample" for "hospital" wherever you can find it in the script. - On lines 5 and 7 of the script, the location of your database is indicated, as well as its log file. Make sure that the location corresponds to how it is set up on your own computer. Chances are that the directory will be the same. The folder name "MSSQL10_50.MSSQLSERVER" might be different if your server version differ or if you did not keep the default name when you installed it.
- Execute the script.
The script will add all the tables and data into the hospital database.
You'll need to rename and alter tables below.
When I say 'Rename' a table:
- Right-click on the table name in the Object Explorer and choose Rename.
When I say 'Alter' a table:
- Right-click on the table name in the Object Explorer and choose Design
When I say 'Save' a table:
- Make sure you are on the correct window and CTRL-S or File->Save.
If you remember, the hospital app had one form called the "Patient Info Form" which recorded referrals for patients. So we need a FACT table in our database that stores the fields from the form. Instead of creating a completely new table, we can just rename the already existing 'FACT_Registration' table.
Rename 'FACT_Registration' to 'FACT_referrals'.
Alter the 'FACT_referrals' table. Rename the column 'RegisterationKey' to 'referralsKey'. This column is the primary key for the table, and it should always have the format [FACT table name without FACT_]Key
Also remember from the Basic Project, the following fields are needed in the form:
| Field | Type | Database Data Types |
|---|---|---|
| PatientName (For Patient Name) | Text | varchar(50) |
| Diagnosis (For Diagnosis) | Select | int |
| Gender (For Gender) | Select | int |
| ReferralDate (For Referral Date) | Date | date |
| RefSector (For Referral Facility) | Select | int |
| Status (For Status: Active, deceased and discharged) | Select | int |
| DeceasedDate (For Deceased Date) | Date | date |
| ER2WeeksPrior(For ER2WeeksPrior- Means was the aptient alloted the Emergency Room 2 weeks Prior to death) | Check box | int |
| Register | Button | NA |
| Back | Button | NA |
Notice that Select fields are stored as int. We need a column in the FACT_referrals that corresponds to each field (except the buttons).
The FACT_referrals table will already have columns because we copied it from a sample app database. Delete these columns except for addedBy. (The addedBy column records which account added the referral). You can run a SQL query to do this, or you can right-click on the column name in the Object Explorer and chose Delete.
Alter the FACT_referrals table. Add a new column for each field in the table above (except the buttons). Make sure the Data Type for the column is the same as the table above. Also make sure the names of the columns are correct. They should be the same as the table above and the 'name' attribute in your html code.
Save the FACT_referrals table
##FACT_teamMembers##
The FACT_teamMembers table stores the information about the logins for the app. For now there is only a Administrator account. When we have the app running we can add the nurse account from the app itself.
##LKUP_userRole##
This lookup tables stores the values for the different kinds of account.
Edit the rows of the table. Right-click on the table name in the Object Explorer and choose 'Edit Top 200 Rows'. In the results that open up, the table will contain the different user roles. For the hospital app we need two types of users:
a. Administrator (Username: admin, Password: admin)
b. Nurse (Username: nurse, Password: nurse)
The table should already have a Administrator userRole. Edit the other row (you can just click on the cell and type) so the userRoleLabel = 'Nurse'.
##Other LKUP Tables##
###Creating the LKUP Tables###
We need a LKUP table for each column in the FACT_registration table that corresponds to a Select (drop-down list) field. There are four such fields (Diagnosis, Gender, RefSector, Status), so we need one LKUP table for each.
As you might have noticed from the LKUP_userRole table and form the Naming Conventions page, LKUP tables should have three rows.
Let's create a LKUP table for Diagnosis.
In the Object Explorer expand your database, and right click on Tables. Select New Table. Add the following columns:
| Column Name | Data Type | Allow Nulls |
|---|---|---|
| DiagnosisKey | int | No |
| DiagnosisLabel | varchar(100) | Yes |
| DiagnosisOrder | int | No |
Then, right click on 'DiagnosisKey' and chose "Set Primary Key". Save the table by pressing CTRL-S or File->Save. Name the table 'LKUP_Diagnosis'.
Create LKUP tables for RefSector using the same steps as above, but with using RefSector where ever you used Diagnosis. Save it as LKUP_RefSector.
Note that you don't have to create new tables for Status and Gender, as those should already exist (since they existed in the sample database we copied from).
###Editing the LKUP Tables###
Now we need to add the rows to each LKUP table so they list the options we want in the form.
Right-click on the LKUP_Diagnosis table in the Object Explorer and choose 'Edit Top 200 Rows'. In the window that opens, you can add rows. Start the Key from 1 and increment by 1 for each new row. The Label is what is displayed in the drop-down on the form. For now just add three rows with the Labels: 'Not Selected', 'Flu', 'Bronchitis', 'Cardiac Arrest'. The Keys should go from 0 to 3. The Order column determines in which order the choices are displayed in the drop-down. Number them in the order you want the choices displayed, but make 'Not Selected' be the first choice so it is chosen by default. (You can put -1 if you don't want to display a choice).
Edit the other three LKUP tables in the same way.
- For the LKUP_RefSector have the Labels: 'Not Selected', 'Glebe Center', 'East Ottawa Center', 'West Ottawa Center'.
- For the LKUP_Status table have the Labels: 'Not Selected', 'Active', 'Discharged', 'Deceased'.
- Make sure the LKUP_Gender table has the Labels: 'Not Selected', 'Male', 'Female'.
Now that we have all the LKUP_Tables we need, delete the extra ones that were copied from the sample database. (Right-click on table and select delete.)
You should have 8 tables in total: 3 FACT tables (queries, referrals, teamMembers) and 5 LKUP tables (Diagnosis, Gender, RefSector, Status, userRole).
Quickforms apps use the queries stored in the FACT_queries table to display the data in tables and graphs in the app.
Right-click on FACT_queries and chose Edit Top 200 Rows. Look at the row with the quertLabel = 'getTableDisplay'. This should be the query you are using to display the table on the forms.html. The query will not work as it is right now, since it is copied from sample database. We want the query to display the following table:
The query needs to select five columns. The following query should do what we want:
SELECT r.referralsKey as id, 'registerationform' as form,
r.ReferralDate as 'Referral Date',
r.PatientName as 'Patient',
a.DiagnosisLabel as 'Diagnosis',
b.RefSectorLabel as 'Referral Facility',
c.StatusLabel as 'Status'
FROM FACT_referrals as r
LEFT JOIN LKUP_Diagnosis as a on r.Diagnosis = a.DiagnosisKey
LEFT JOIN LKUP_RefSector as b on r.RefSector = b.RefSectorKey
LEFT JOIN LKUP_Status as c on r.Status = c.StatusKey
%WHERECLAUSE% order by r.referralsKey desc;Note the LEFT JOIN statements which show allow the labels for the drop-downs to be shown properly.
You'll need to change and create new queries that the hospital app uses, so that they make sense with the hospital database tables and column names. For example you'll need to create getReferralsTable, and getReferralsGraph for the reports page.
You'll also have to change the .js files in your js folders so that the tables and graphs are formatted properly. For example choosing the width of the columns.
When you did the Basic Project, you'll have added a resource for the hospital app. Change it so it points to your local database. It should be like this:
<Resource driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" maxActive="15" maxIdle="5"
maxWait="5000" name="jdbc/hospital" password="yourPass" type="javax.sql.DataSource"
url="jdbc:sqlserver://localhost:1433;autoReconnect=true" username="yourUser" />Where:
- name = "jdbc/hospital" (for other apps put the name of database and app here)
- username = username for your local SQL Server Authentication
- password = password for your local SQL Server Authentication
Open the app by using the html http://localhost:8080/hospital/
Login using the admin account. Go to Team Members tab and add a user with the user role Nurse, and username=password='nurse'.