Views and triggers are part of SQLite, so should be available on Android as well as other platforms. I don’t know much about Android, but from a quick Google it looks like Room is a database interface layer and I’d be very surprised it there aren’t others.
I’d seriously suggest taking a look at the SQLite documentation for views and triggers as, despite views being almost, but not quite the same as tables and hence somewhat quirky, they can be very useful. What the documentation doesn’t describe is how best to use views, which is really more to do with data modelling than programming or databases per se.
For what it’s worth, my general approach is start by looking for “real world things” – physical or conceptual entities – and work out what tables are needed to store them. If you look through introductory texts you’ll often see examples involving students and professors and courses, or departments and employees. Generally these, at least in my view although obviously not the author’s, are at best misleading or at worst utterly wrong. Please take them as lessons in SQL programming, not database design.
A student isn’t a real world thing, he or she is a person. The “student” part of it arises because a person can be registered at an academic institution, i.e. it’s the relationship person and the institution that establishes that the person is a student. In fact it’s the relationship that carries them some typical “student attributes” (like a student number). It’s also the presence of the relationship that allows the student to enrol on a course. Similarly the professor is a person who is engaged by the academic institution. Being a professor, or a reader or a lecturer or a tutor is an attribute not of the person, but of that relationship.
Students and professors are the same, basic, “real world” entity; a person who happen to participate in one of two different relationships, perhaps even both at the same time. Customers, clients, vets, farmers, cut them and they bleed, they’re all the same; what differentiated them is not what they are, but how they relate to the world around them.
Even then, you shouldn’t throw everything about a person into a Person table. Students and Professors both have a date-of-birth, but they don’t have telephone numbers; telephones have numbers, not people. Neither do they have addresses, houses do. And they change with time.
Relational databases don’t do time variant data as a fundamental structure (you can’t do something like CREATE TABLE Animal ( ... weight *TIME VARIANT* NUMBER ... ); and have the database do the hard work). But Animals grow, so if you want to record their weight it needs to be in a separate Weight table. In data modelling parlance Weight is a weak entity; the rows of the table have no identifier of their own, just that of the animal to which they relate. If you delete the animal, you want all its weight records to be deleted too (see referential constraints).
Similarly – as you clearly already know – reference data like sex should be in a separate table (by the way, if it’s not already, you might want to make that relationship many to one, I presume your wethers weren’t born that way!).
Anyway, the point is that even what looks like a simple concept – student, professor, person, animal – will inevitably normalise into a collection of tables; on top of which the application really does want to know about students and professors, sheep and goats and pigs.
This is where views come in.
Personally I build layers of views that culminate in the application level object that I want. For example, I might well build a Person view that pulls together a useful definition of a person that includes their *current *contact details, their current weight (or whatever) as well as their date-of birth. I would then use that view to create further views for Student and Professor by joining against the relationships Persons have. These views would bring in relevant information from the relationships (associations) that define the person as being a student or a professor.
Views are also very useful for isolating sub-sets, for example the subset of Animals that are Sheep and the subsets of Sheep that are Lambs or Ewes or Wethers or Tups. SQLite was designed to be an alternative to building complex runtime data models and serialising/ deserialising them to files. Because it runs in-process SQLite is very fast (certainly compared to a conventional database, no network latency or marshalling I/O ), so using it to answer even very simple questions can make sense. Views can make applications simpler even for apparently straightforward questions like “What percentage of my flock are ewes that are more than 5 years old?”.
I don’t wish to give the impression that database views are a panacea for all ills, they come with problems that shouldn’t be understated. It’s easy to use views to pull data together and retrieve them from the database. It can be a real problem reversing that process, inserting into or updating the database. The underlying problem is that queries (and that’s all views are) don’t have to include the primary key(s) of the table rows from which they pull the data in their result set. That means that if you then want to update a row or insert a new one through the view, the database doesn’t have a primary key by which to identify it. Having a primary key is mandatory. In consequence the basic position of SQL databases is, I can’t insert or update views in the general case, so I won’t let you do it at at all.
Now there are circumstances where it could be done, typically if the view includes the PK or (for inserts) it is autogenerated. INSTEAD OF INSERT and INSTEAD OF UPDATE triggers on views pass the buck to the programmer, but they are a starting point. The principal problem is, what do I use as a primary key? Using a natural key, i.e. some combination of mandatory attributes that is guaranteed to be unique, sounds like it should be easy, but in practice it often isn’t. Think, for example, of an animal’s ear tag number. Definitely unique, but to use it as a natural key is to DEMAND it’s available before you can enter any data about that animal. Might be Ok, might not. On the other hand using a surrogate key, e.g. a row count or a uuid, imposes one of two problems: if the database allocates the key, you’ve somehow got to pass the keys of newly created rows back to the application; conversely, if the application allocates keys, you have to make sure that they are consistently created by all applications that may ever use the database and can’t clash.
By now you’ll be screaming enough, enough, so I’ll stop.
