Database design of "Android Development instance" Time management app development

Of course, you can write the interface or something first, but always feel that the database is written in the background to write the interface more assured.

For the design of the database, I had no idea at first, and I didn't even know how to do it, and I started thinking about how to design a few tables. What fields are there?

It took two days to finish.

Finally, referring to the database system introduction of some of the lessons and view the source code of other projects, it is likely to determine the database.

Because the category of this app is determined by me to be only a level two category. So I designed three tables:

Total class table, subclass table, detail record table.

(The log statement that appears in the program code is used only for my own test output)

The code is as follows:

Package suool.net.timesumlbxf.db;/** * Created by Suool on 2014/10/6. */public class Timesumdbinfo {private static String tablenames[] = {"Tbl_expenditure_category",// Time spent category database table "Tbl_expenditure_sub_category",//time spent sub-category database table "Tbl_expenditure"//Time Cost database table};//table name private static String fieldnames[][] = {"id", "name"}, {"id", "name", "Parent_ca Tegory_id "}, {" ID "," AMOUNT "," expenditure_category_id "," expenditure_sub_category_id "," DATE "," Time "," MEMO "}};//field name private static String fieldtypes[][] = {" INTEGER PRIMARY KEY autoincrement "," Text "}, {" Integer PRIMARY key AutoIncrement "," TEXT "," Integer "}, {" Integer PRIMARY key AutoIncrement " , "DOUBLE", "Integer", "Integer", "Text", "text", "text"}};//field type Public timesumdbinfo () {// TODO auto-generated Constructor stub} public static string[] GetTableNames () {return tablenames;    } public static string[][] Getfieldnames () {return fieldnames;    } public static string[][] Getfieldtypes () {return fieldtypes; }}

This is the database basic properties, the specific database creation and method abstract are as follows:

Package Suool.net.timesumlbxf.db;import Android.content.contentvalues;import Android.content.context;import Android.database.cursor;import Android.database.sqlite.sqlitedatabase;import Android.database.sqlite.sqliteopenhelper;import Android.nfc.tag;import Android.util.log;import android.widget.toast;/** * Created by Suool on 2014/10/6.    */public class DBHelper extends Sqliteopenhelper {public static final String TAG = "MyTest";    Private DBHelper Mdbhelper;    Sqliteopenhelper Instance Object private Sqlitedatabase mDb;     Database instance object private static DBHelper Openhelper = null;//Database Call instance private static String tablenames[];   Table name private static String fieldnames[][];   Field name private static String fieldtypes[][];    field type private static String No_create_tables = "NO TABLES";    private static String message = "";    Private final Context Mctx; Context Instance//construction Method Private DBHelper (context context, String Mydbname, sqlitedatabase.cursorfactory factory, int version       ) { Super (context, Mydbname, Factory, version);    Mctx = context;                                       }//Get DBHelper instance public static DBHelper getinstance (context context, String mydbname,  Sqlitedatabase.cursorfactory Factory, int version) {if (Openhelper = = null) {Openhelper = new            DBHelper (context, Mydbname, Factory, version);            Tablenames = Timesumdbinfo.gettablenames ();            FieldNames = Timesumdbinfo.getfieldnames ();        Fieldtypes = Timesumdbinfo.getfieldtypes ();        } log.d (TAG, "" + tablenames.length);    return openhelper; }//CREATE DATABASE action @Override public void OnCreate (Sqlitedatabase db) {if (tablenames = = null) {mess            age = No_create_tables;            LOG.D (TAG, message);        Return             } for (int i = 0; i < tablenames.length; i++) {String sql2 = "CREATE TABLE" + tablenames[i] + "(";  for (int j = 0; J < Fieldnames[i].length; J + +) {              SQL2 + = Fieldnames[i][j] + "" + Fieldtypes[i][j] + ",";            } sql2 = sql2.substring (0, Sql2.length ()-1);            SQL2 + = ")"; LOG.D (TAG, "Automatic assembly of SQL statements.")            +SQL2);  Db.execsql (SQL2);    Execute statement} log.d (TAG, "database creation succeeded.");  Update database @Override public void Onupgrade (sqlitedatabase db, int oldversion, int. newversion) {for (int i = 0; I < tablenames[i].length ();  i++) {String sql = "DROP TABLE IF EXISTS" + tablenames[i];        exist delete db.execsql (SQL);   } onCreate (db); Execute CREATE DATABASE statement}/** * Add database related information */public void Inserttables (string[] tablenames, string[][] FieldNames, St        Ring[][] fieldtypes) {tablenames = Tablenames;        FieldNames = FieldNames;    Fieldtypes = Fieldtypes;    /** * Close Database */public void Close () {mdbhelper.close (); } public void Execsql (String sql) throws Java.sql.SQLException {Mdb.execsql(SQL); }/** * SQL statement Query data */public Cursor rawquery (String sql, string[] selectionargs) {cursor cursor = MDb        . rawquery (SQL, Selectionargs);    return cursor; /** * Query Data */Public Cursor Select (string table, string[] columns, string selectio  N, string[] Selectionargs, String groupBy, String having, string by) {CURSOR cursor = Mdb.query (table, columns, selection, Selectionargs, GroupB        Y, having, by-and-by);    return cursor;  /** * Add data */public long Insert (string table, String fields[], string values[]) {contentvalues CV        = new Contentvalues ();        for (int i = 0; i < fields.length; i++) {cv.put (fields[i], values[i]);    } return Mdb.insert (table, NULL, CV); }/** * Delete data */public int Delete (string table, string where, string[] whereValue) {return mdb.delete (table, where, wherevalue); }/** * Update data */public int update (string table, String updatefields[], string updateval        Ues[], String where, string[] wherevalue) {contentvalues CV = new Contentvalues ();        for (int i = 0; i < updatefields.length; i++) {cv.put (updatefields[i], updatevalues[i]);    } return Mdb.update (table, CV, where, wherevalue);    }/** * Error message: Non-null database not established */public String getMessage () {return message; }}

The

Data-related default field values are as follows, and you are planning to implement custom additions and deletions for these items.

<?xml version= "1.0" encoding= "Utf-8"?><resources> <!--time Spending project--<string-array name= "Tbl_expen Diture_category > <item> Learning </item> <item> work </item> <item> Entertainment </item&        Gt     <item> Happy Hour </item> <item> daily mission</item> </string-array> <!--time spending sub-items-- <!--Learning sub-items--<string-array name= "Tbl_expenditure_sub_category_1" > <item> base Language </item&gt        ; <item> scripting language </item> <item>linux programming </item> <item>Android</item> <i tem> Technical books or blogs </item> </string-array> <!--work Items--<string-array name= "Tbl_expenditure_su B_category_2 "> <item>APP</item> <item> Outsourcing program </item> </string-array> &lt ;!        --Entertainment sub---<string-array name= "Tbl_expenditure_sub_category_3" > <item> computer Games </item> <item> Mobile Games </item> <item> social networks </item> <item>Music</item> </string-arra Y> <!--fun time Photon project--<string-array name= "Tbl_expenditure_sub_category_4" > <item> Literature Reading </    item> <item> Blog Writing </item> <item> workout </item> <item> accompany Li Yi </item> </string-array> <!--Daily Task List--<string-array name= "Tbl_expenditure_sub_category_5" > <it EM&GT;2 algorithm Topics </item> <item> words 50 or English reading </item> <item> technical posts 1 </item> <ite M> Technical Articles + notes </item> <item> ebook 1 hours </item> </string-array> <!----> <string- Array name= "Hour" > <item>0</item> <item>1</item> <item>2</item&gt        ;    <item>3</item> <item>4</item> <item>5</item> </string-array> <string-arrAy name= "Minute" > <item>0</item> <item>10</item> &LT;ITEM&GT;20&LT;/ITEM&G        T <item>30</item> <item>40</item> <item>50</item> </string-array> </resources>

In the length of the above is fixed by me, in fact, the user input any value, I have just started to consider the user input values, but later, I think this time is not very accurate thing (to do something specific beginning and end of the boundary is unclear, and a period of time is not necessarily used to do something), So instead of letting the user spend time calculating accuracy, it's better to give a ballpark choice. (rounded).

OK, the design of the database is this way, to finish this feeling in fact the design of the database is not difficult, as long as they can analyze the focus of what is done in the good.

The app's core data is actually a record, which includes category, time, time, and so on field values.

However, it is not possible to design only one table, because designing a table is not conducive to querying and results in data redundancy. As a result, the database is designed to look like what I have shown in the code above.

Of course, this is just to provide my ideas, other better design must exist, I hope you can guide learning.

Database design of "Android Development instance" Time management app development