Step 6: Building the Data Model
You can build your data model using different methods. You can build your data model where the Data Shapes match with your tables, but what you'll realize over time is that you will often need custom Data Shapes. When you query for data, you will often need data from differing tables to be in one result set. Because of this, I suggest against making your data model based on the tables. You can start the basis for your model with the tables in mind, but know that this won't be the basis for long.
Create Data Shapes
Let's start by setting up two queries and the Data Shapes to match. The first query will be to find the list of classes a student is assigned to and query will be completed here. The second query will be to find all student in an active class and we'll ask you to create it based on how we did the first one.
1. In the ThingWorx Composer, click the + New button in the top left.
2. In the dropdown list, click Data Shapes.
3. Name the Data Shape DataShape.StudentCourses
4. Add the set of fields below. You may notice, we included information from the Person table that we might already have. This is perfectly fine based on how much data you expect to come back. This will allow you to reuse this database for other purposes later where the person information might change. This can be very beneficial when you're calling a service with this Data Shape (or InfoTable based on this Data Shape). Allowing you to keep the input simple.
| Name | Base Type | Additional Info |
| id | STRING | Primary Key |
| person_key | STRING | N/A |
| person_name_first | STRING | N/A |
| person_name_last | STRING | N/A |
| course_key | STRING | N/A |
| course_name | STRING | N/A |
| course_professor | STRING | N/A |
Let's add in our database query and use our new Data Shape.
1. Open the DatabaseController.Facilities entity and go to the Services tab. If you have not done so as yet, add the configuration information to allow your queries to connect to a database.
2. Create a new service of type SQL (Query) called GetStudentEnrollment.
3. Click Save and Continue to save your changes.
4. Add a parameter to the service title email. It will have a String base type and be required.
5. Add the following query to the canvas.
SELECT person_key, person_name_first, person_name_last, course_key, course_name, course_professor
FROM Person person
INNER JOIN PersonCourses pc ON person.person_key = pc.person_key
INNER JOIN Courses courses ON courses.course_key = pc.course_key
WHERE person.person_email = [[email]]
6. For the output of the service, InfoTable should be there by default. If not, switch the output to be an InfoTable. For the Data Shape, set it to the Data Shape we just created, DataShape.StudentCourses.
You now have a database where you can run your queries and use the responses to bind to Widgets in Mashups.
We have our database connection and a data model setup to handle our current queries. This might be where you begin to question if you would like to add Data Tables. This is more of a design choice. You might want to keep datasets in a Data Table for quick access or separation. Nevertheless, if you already have you database, you won't need many (if any) Data Tables.
Step 7: Next Steps
Congratulations! You've successfully completed the guide for Connecting to an External Database, and learned how to use the ThingWorx Platform to connect to database, query for data, and write new data.
The next guide in the Utilizing ThingWorx to Secure Your Aerospace and Defense Systems learning path is Low Level Device Connection.
Learn More
We recommend the following resources to continue your learning experience:
| Capability | Guide |
| Build | Design Your Data Model |
| Build | Configure Permissions |
Additional Resources
If you have questions, issues, or need additional information, refer to:
| Resource | Link |
| Community | Developer Community Forum |
