SQL syntax to combine several tables

Question

I need to display a single list, ordered by date which contains different types of data, images, video and whatnot.

I guess you could have one separate table for each type and the use something like FULL OUTER JOIN (simulated as sqlite doesn't support it) and sort on date. But this would be complicated because I still need to have unique IDs across all tables.

Maybe having a master list which has ID and date, and then JOIN in the types tables? But how bad is the performance of this using sqlite? I really like to avoid having a super table which contains columns all the combined types could ever need.

And ideas?

Answer 1

Is this something you want to do in database? I am guessing in code you map the data into different kinds of objects (e.g. class Video, class Image...) so you could the data combining there as well.

Answer 2

You should try to build and see what performance hit you get.
SQLite is really fast engine, on mobile devices the flash memory is slower. But even with ten-thousands of records you get fast queries.

You shouldn't monkey with the performance hit until you don't have it.

Answer 3

There are three patterns I know of for mapping class hierarchies to tables of a relational database:

1. One table for the entire hierarchy. Every row will contain the union of all the attributes of the classes of the hierarchy. This is in my opinion hard to maintain if there are changes to the classes. Every row needs a type identifier, of course.
2. One table for every concrete class of the hierarchy. In that case, a table that represents a concrete class contains the attributes of this class plus the ones of all abstract base classes. Abstract classes are not mapped directly.
3. One table for every abstract and concrete class. In that case, every single class is directly mapped to a single table.

The advantage of 1. is that it is fairly easy to achieve what you want to do, i.e. read all objects that are part of your class hierarchy. You waste some space, though. In 2., you could read every table in a sub select of one select statements with constant dummy values for the attributes of the other classes, then UNION them together in a single SQL statement. In 3. the query gets a little more complicated: you have to INNER JOIN the concrete class tables with the base-class tables in the subselects. In neither case would you need a FULL OUTER JOIN, though.

---

Addendum:

Suppose you have an object hierarchy with a Person class and two subclasses, Student and Teacher. In 2., you'd create two tables, STUDENT and TEACHER, with both have a column NAME. The STUDENT table has an additional column YEAR, while the TEACHER table has a column SALARY. Your query might look like this:

    SELECT 'STUDENT' AS TYPE, NAME, YEAR, NULL AS SALARY FROM STUDENT 
UNION SELECT 'TEACHER' AS TYPE, NAME, NULL AS YEAR, SALARY FROM TEACHER

which you can then retrieve and use to fill your objects with data or display using a SimpleCursorAdapter or whatever you like.