Alarm and Alarm instances tables in default DeskClock application

  • Replies:2
  • OpenNot stickiedUnanswered
  • Forum posts: 3

Nov 20, 2014 1:47:02 PM via Website

I am trying to learn and understand design patterns and approaches of developing responsible, reliable and well-coded applications. Now I am studying default DeskClock applications (comes with 4.4 and higher). I have noticed such weird thing for me. There two tables for storing alarms : alarms and alarms instances.
Here is attributes of this tables.

private static void createAlarmsTable(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE " + ALARMS_TABLE_NAME + " (" +
            ClockContract.AlarmsColumns._ID + " INTEGER PRIMARY KEY," +
            ClockContract.AlarmsColumns.HOUR + " INTEGER NOT NULL, " +
            ClockContract.AlarmsColumns.MINUTES + " INTEGER NOT NULL, " +
            ClockContract.AlarmsColumns.DAYS_OF_WEEK + " INTEGER NOT NULL, " +
            ClockContract.AlarmsColumns.ENABLED + " INTEGER NOT NULL, " +
            ClockContract.AlarmsColumns.VIBRATE + " INTEGER NOT NULL, " +
            ClockContract.AlarmsColumns.LABEL + " TEXT NOT NULL, " +
            ClockContract.AlarmsColumns.RINGTONE + " TEXT, " +
            ClockContract.AlarmsColumns.DELETE_AFTER_USE + " INTEGER NOT NULL DEFAULT 0);");
    LogUtils.i("Alarms Table created");
}

And for instance table

 private static void createInstanceTable(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + INSTANCES_TABLE_NAME + " (" +
                ClockContract.InstancesColumns._ID + " INTEGER PRIMARY KEY," +
                ClockContract.InstancesColumns.YEAR + " INTEGER NOT NULL, " +
                ClockContract.InstancesColumns.MONTH + " INTEGER NOT NULL, " +
                ClockContract.InstancesColumns.DAY + " INTEGER NOT NULL, " +
                ClockContract.InstancesColumns.HOUR + " INTEGER NOT NULL, " +
                ClockContract.InstancesColumns.MINUTES + " INTEGER NOT NULL, " +
                ClockContract.InstancesColumns.VIBRATE + " INTEGER NOT NULL, " +
                ClockContract.InstancesColumns.LABEL + " TEXT NOT NULL, " +
                ClockContract.InstancesColumns.RINGTONE + " TEXT, " +
                ClockContract.InstancesColumns.ALARM_STATE + " INTEGER NOT NULL, " +
                ClockContract.InstancesColumns.ALARM_ID + " INTEGER REFERENCES " +
                    ALARMS_TABLE_NAME + "(" + ClockContract.AlarmsColumns._ID + ") " +
                    "ON UPDATE CASCADE ON DELETE CASCADE" +
                ");");
        LogUtils.i("Instance table created");
    }

I have found only some comments in the code.

/**
* Constants for the Instance table, which contains the state of each alarm.
*/

And

/**
 * Constants for the Alarms table, which contains the user created alarms.
 */

So if instance table contains state of each alarm. Okey , we will store state of alarm in this table. Here is ALARM_STATE attribute, and there is no ENABLED. Also there is no date in alarm table. I don't understand logic of doing in such way. Why should we repeat attributes in both tables. Why not to store only state in instance table.
Please explain this design and approach.
What are pros and cons ?
Thanks everyone in advance.

  • Forum posts: 118

Nov 20, 2014 9:14:26 PM via App

I dont see the point of having some of the fields in both tables but the reason why there are two tables is to reduce duplicated values.

For example: if you create a user table with a field name country, then it would unnecessary to constantly enter the same value all over again. instead you can create a second table with only two fields. ID and the name of the country. You can put the id of the country in the user table country field and reduce space and loadtime for each query request because the name of the country will only be entered once in the database.

You should look more into Sql queries and how to efficiently create/use tables. Also to create databases on android easier, take a look into Sugar ORM.

  • Forum posts: 3

Nov 21, 2014 6:08:15 PM via Website

Thank you for answer , I will look closer at queries and use of this two tables in this application. I know that it is a good practice to store duplicates in separate table and only store id in main table . And there is one step of normalization. But here it seems weird for me. I will study this better and pos the results.