OrmLite – Lightweight ORM Package + Android + *<—>* relationship?

My most recent adventure in Android/Multi-platform applications is CrickBoard! It is hosted on github.com and is licensed under GPLv3. The application is still under construction and there will be some time before it becomes fully operational. Most of the work is remaining in the UI and further utility functions related to UI data handling. This also is my first application following Google’s Material Design guidelines. I initially had planned to use a multi-platform SDK so that it can run seamlessly on at least Android, iOS and WP. But that would branch out later I guess as my first target is Android with the material design. I am using some design libraries that I got from Android-Arsenal.com. Most of these libraries are open source licensed under very constructive opensource licenses. So for the record, as of now, CrickBoard is using MikePenz’s MaterialDrawer and Dexafree’s MaterialList. Both are great components providing ready to use material design libraries implemented and updated according to Google’s guidelines.

For the database part of it, I wanted to do it using SQLite directly. It turned out to be quite cumbersome especially when the encapsulation/serialization of data was not being handled in a single class. Of course making the data classes more complicated would have worked, but I generally follow the KISS principles. I was searching for a lightweight ORM/DBMS library that can work on Android (and possibly other platforms) and provides me with the needed encapsulation and serialization possibilities. I stumbled upon OrmLite, a very light weight Object Relational Mapping java package and believe me, it is amazing. It has power Database Access Objects (DAOs), supports multiple DBs, native calls to Android DB APIs, and a whole lot more. The best part is that one simply needs to configure general classes as needed and with very little DB specific coding. Creation of DBs, relationships between various tables and elements, etc. can all be done in a more descriptive language. Also it can be easily learned. The only thing which is a bit weird and took some time for me to understand is the 1—>* (1 to many or has-a relationship) and how it is implemented in OrmLite.

There are some examples given for account and orders with one account holding multiple orders. If one does not use OrmLite, the orders per account needs to be filled up manually or using simple queries. Also the encapsulation of the retrieved data would have to be handled in a separate manner with either utility/helper functions/classes which would implement the code to connect the orders per account alternation. But using OrmLite, one can use the ForeignCollection<> in account. The peculiarity is that the order now has to have a ForeignField account. This is not quite visible at first until you run into weird errors. Also one of the things that you cannot do is populate the ForeignCollection<> field in the account table yourself. This will be filled up automagically by OrmLite. Looking into more details, it seems entirely logical to avoid the complexity of raw SQLs and data filling yourself. But sometimes, it still feels as if much information will be duplicated because of this strategy.

For example, in CrickBoard, a player can be a part of multiple teams. i.e. a 1—>* relationship between player and teams. Also, each team can have multiple players i.e. 1—>* relationship between team and player. The latter is an easy solution where the team has a ForeignCollection<> of players and each player has a ForeignField of player. BUT there is no such easy solution for the former part. Since each player can only have 1 ForeignField of team, the player is restricted to only 1 team. The problem is I can’t have a ForeignCollection<> of teams in the players (indicating that the same player can be part of multiple teams). Hence, the way forward for me is to create the tables in the following manner. Of course, these scheme will make things complicated in terms of queries and DB updates.


Player (1) –> Intermediary1 (*) <—> Intermediary2(*) –> Team(1)


If I follow the above schema, it will give me the possibility to have a many to many relationship to have the same player in multiple teams and a team having multiple players. Of course there will be code duplication as well as some complex SQL queries generated because of the above. I know it gives away the KISS principles but for the time being, I see this as the only way forward. Duplicating Players is not an option for me. Any other suggestions are most welcome. I will try to implement them in CrickBoard and see if it does not complicate much. Maybe I can reduce one of the intermediary tables and have only 1 in the middle (MatchPlayers?). More updates will come as and when I progress. In the meantime, do checkout the project and feel free to contribute to it.

Cheers, Naresh