Android Tutorial Database Design - Introduction

This tutorial describes the steps I took to design the database for the Pre-Algebra Tester app available on Google Play.

This is Part 1 of a potential series that will describe how I designed, implemented and released a client-based database app. Depending on the response to this tutorial, I may add additional parts to it. Note: the terms "primary key", "foreign key" and "database schema" are used in this tutorial. If you don't know what these terms mean, it would be a good idea to familiarize yourself with them before continuing.

[1.] Tools to design your own client-based database app for android

SQLite

SQLite Expert Personal - a nice Windows ui for creating and manipulating databases (Optional - but I liked having it, because I find working from the command line slows me down.) However, I definately do not know how to use most features of this software. I'm not the person to ask questions about use.

MS Access - Optional
I used access for this tutorial to: provide additional documentation and create a visual representation of table relationships - database schema. You don't need it at all. I designed my original tables and database schema with pencil and paper and used the pencil and paper schema design for the entire life cycle of the project up to and after release.

[2.] Define app Functionality

[2.a] The app will allow the user to test themself by answering several different types of pre-defined Pre-Algebra questions. The same questions will not be displayed over and over. The question types will be the same but numerical components and/or answers will be generated by a random number function.
[2.b] The user will be able to create and save custom tests using a Test Builder feature.
[2.c] Test results from Standard and Test Builder tests can be saved to and retrieved from a database.

[3.] Database Design - Tables

The the design of the database is application specific. Even though the design is application specific, redundancy should be eliminated in database design. I was able to approach table design and relationships from a heirarchical standpoint - most general tables to most specific. Database design definately takes some practice.

NOTE: The data types shown in the table designs are Access data types, they are not SQLite data types. The actual data types used in SQLite were Numeric and Text.

[3.a] The Test Definition Side

[3.a.1] There are two main types of tests: Standard and Test Builder. This is reflected in the [test_type] table. See test_type_A.png and test_type_B.png below.
[3.a.2] We know the user can take/create different tests, therefore a [test] table was created. See test_A.png and test_B.png below.
[3.a.3] Tests are made up of test questions. The [test_question] table is made up of questions associated with a given test or tests. This table is important becuase individual questions can occur in more than one test. See test_question_A.png and test_question_B.png.
[3.a.4] There is a finite number of question types that the user can choose from in this application. The [question] table contains all of the different question types. See question_A.png and question_B.png.

[3.b] The Test Results side

[3.b.1] When a user takes a test and saves the result a test result is created. The [test_result] table is a summary level table that contains one record for each test result. See test_result_A.png and test_result_B.png.
[3.b.2] If a user takes a test and decides to save the test result there will be individual question information saved if the user did not get zero wrong. Assuming the user got 1 or more questions wrong then a question result will be created for each question the user answered wrong. The [question_result] table saves what I considered to be the relevant information regarding an incorrect answer to a question. See question_result_A.png and question_result_B.png. - next posting.

[4.] Indexes, Constraints and Triggers

None of the tables in this database are indexed. You need to take factors such as table size into consideration when deciding how many (if any) indexes you will create for your database. Given the small relative size of the tables in this database I decided that indexes would not be worth maintaining for this db. There is performance overhead associated with index creation. In some cases indexes will actually slow performance down.

Constraints and Triggers are not used, but could have been.

[5.] Database Design - Schema

The Database Schema shows the logical relationship (linking fields) of tables in a database. See schema_pat.png - next posting.

[6.] Design Post Mortem

I am pleased with my database design regarding how it has served my android app to this point. I have not had to change the db design since app release. I'm sure this is in part due to the fact that the design is very simple. I believe this application to be open-ended and flexible regarding my original functionality specification. Specifically, I can see how additional tests, questions and test types could be added for the user by using an automated process that added records to the appropriate tables without having to change a single line of b4a code in the Pre-Algebra Tester!
 

Attachments

  • test_type_A.PNG
    test_type_A.PNG
    4.3 KB · Views: 370
  • test_type_B.PNG
    test_type_B.PNG
    1.3 KB · Views: 358
  • test_A.PNG
    test_A.PNG
    10 KB · Views: 340
  • test_B.PNG
    test_B.PNG
    4.9 KB · Views: 355
  • test_question_A.PNG
    test_question_A.PNG
    5.7 KB · Views: 329
  • test_question_B.PNG
    test_question_B.PNG
    11.6 KB · Views: 323
  • question_A.PNG
    question_A.PNG
    4.1 KB · Views: 367
  • question_B.PNG
    question_B.PNG
    13.1 KB · Views: 328
  • test_result_A.PNG
    test_result_A.PNG
    5.9 KB · Views: 317
  • test_result_B.PNG
    test_result_B.PNG
    2.1 KB · Views: 303
Last edited:
Top