Efficient way for Inheritance Table schemes

Question

I'm planning to display an listview of CarServiceEntries. The CarServiceEntry class contains basic data of a service:

@Entity
abstract class CarServiceEntry(
    private int id;
    private Date date;
    private float odometer;
    
    /*
    getters and setters constructor....
    */
)

CarServiceEntry is abstract as the classes, which inherit it, have more detailied information:

@Entity
abstract class Income extends CarServiceEntry(
    @Converter(....)
    private PaymentType paymentType;
    private float totalAmount;
    
    /*
    getters and setters constructor....
    */
)

The issue arrises with building the SQLScheme. As I want to load all entries TOGETHER rather than making an own sqltable for each CarServiceEntry and query each table (getAllIncomes() getAllExpenses() etc), how can I load every CarServiceEntry(income,expense, service) using one sqltabletable for loading (if possible). The current approach, which I'm not a fan of, looks like this:

CREATE TABLE CarServiceEntry(
    id INTEGER PRIMARY KEY,
    serviceType TEXT CHECK CONSTRAINT (....) //expense, income, service)
    date,
    odometer,
    
    /*
    A LOT of fields as each service has its own "unique" fields and I put it all together which I don' really like :( loading 30+ fields just for one sql statement is not something I like, unless I don't have any other option.
    */
)

I'd rather search for a solution like this:

CREATE TABLE CarServiceEntry(
    id INTEGER PRIMARY KEY,
    date DATE,
    odometer NUMBER
)

CREATE TABLE Income INHERITS CarServiceEntry(
    paymentType TEXT,
    totalAmount NUMBER
    /*some other fields*/
)

CREATE TABLE Expense INHERITS CarServiceEntry(
    location TEXT
    totalCost NUMBER
    /*some other fields*/
)

==>

@Query("SELECT * FROM CarServiceEntry") //this should also return Income and Expense table
Flowable<List<CarServiceEntry>> getAllEntries();

Is there a way to it this way? Or is creating one table with a bulk of fields the only way?

According to: sql inheritance creating an FOREIGN KEY to my subentrytype tables would be kind of inheritance, however this does not solve my issue, as I would still have to need to load every table. I guess the only solution would be using one table with many null value fields?

Answer 1

SQLite doesn't support inheritance and I believe that it will be as simple, if not simpler, to utilise relationships which SQLite and Room support.

Creating multiple tables via room is pretty easy as is creating and handling relationships. So I would suggest taking the typical approach.

Here's an example based upon what I think that yo are trying to accomplish.

First the CarServiceEntry table (which will later have Expenses and Incomes related to it) :-

public class CarServiceEntry {
    @PrimaryKey
    private Long id;
    private String date;
    private float odometer;

    public CarServiceEntry(){}

    .... getters and setters removed for brevity

    /* more convenient constructor (see cse3 in example) */
    /* @Ignore to supress Room warning                   */
    @Ignore
    public CarServiceEntry(String date, Float odometer) {
        this.date = date;
        this.odometer = odometer;
    }
}

• note simplified so TypeConverters aren't required

Next the income table :-

@Entity(tableName = "income",
        foreignKeys = {
            @ForeignKey(
                    entity = CarServiceEntry.class,
                    parentColumns = "id",
                    childColumns = "incomeCarServiceEntryId",
                    onDelete = CASCADE,
                    onUpdate = CASCADE
            )
        },
        indices = {@Index(
                value = {"incomeCarServiceEntryId"}
                )}
        )
public class Income {
    @PrimaryKey
    private Long incomeId;
    private Long incomeCarServiceEntryId;
    private int paymentType;
    private float totalAmount;

    .... getters and setters
}

• note foreign keys = { .... } nor indicies = { .... } are required but suggested that they be used as the help to ensure referential integrity
• Note the additional column incomeCarServiceEntryId this is the ID of the related CarServiceEntry.

Next the expense table (pretty similar to income table):-

@Entity(tableName = "expense",
        foreignKeys = {
                @ForeignKey(
                        entity = CarServiceEntry.class,
                        parentColumns = {"id"},
                        childColumns = {"expenseCarServiceEntryId"},
                        onDelete = CASCADE,
                        onUpdate = CASCADE
                )
        },
        indices = {
                @Index(
                        value = {"expenseCarServiceEntryId"}
                        )}
)
public class Expense {
    @PrimaryKey
    private Long expenseId;
    private long expenseCarServiceEntryId;
    private String location;
    private float totalCost;
    
    .... getters and setters

}

Now a POJO (not a table) for extracting the related data (i.e CarServiceEntry with all of the related Incomes and all the related Expenses) named CarServiceEntryWithIncomeWithExpense :-

public class CarServiceEntryWithIncomeWithExpense {

    @Embedded
    CarServiceEntry carServiceEntry;
    @Relation(entity = Income.class,parentColumn = "id",entityColumn = "incomeCarServiceEntryId")
    List<Income> incomeList;
    @Relation(entity = Expense.class,parentColumn = "id",entityColumn = "expenseCarServiceEntryId")
    List<Expense> expenseList;
}

• YES that's it

Now the Dao's (All in One) AllDao :-

@Dao
interface AllDao {
    @Insert
    long insert(CarServiceEntry carServiceEntry);
    @Insert
    long insert(Expense expense);
    @Insert
    long insert(Income income);
    @Query("SELECT * FROM car_service_entry")
    List<CarServiceEntryWithIncomeWithExpense> getAllCarServiceEntriesWithIncomesAndWithExpenses();
}

The @Database (includes singleton approach) named Database (probably better to use another name) :-

@Database(entities = {CarServiceEntry.class,Income.class,Expense.class},version = 1)
public abstract class Database extends RoomDatabase {
    abstract AllDao getAllDao();

    private static volatile Database instance;

    public static Database getInstance(Context context) {
        if (instance == null) {
            instance = Room.databaseBuilder(context,Database.class,"carservice.db")
                    .allowMainThreadQueries()
                    .build();
        }
        return instance;
    }
}

• Note for brevity and convenience of the demo, the main thread is used.

Finally putting it all together and demonstrating MainActivity.

The demo adds 3 carServiceEntries, with incomes and expenses (the 3rd has none of either). It then extracts the 3 carServiceEntries with all the incomes and expenses. Traversing the extracted and outputting what has been extracted to the log.

:-

public class MainActivity extends AppCompatActivity {

    Database  db;
    AllDao dao;
    private static final String TAG = "CSEINFO";

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        db = Database.getInstance(this);
        dao = db.getAllDao();
        /* Add a Service Entry noting it's ID */
        CarServiceEntry cse1 = new CarServiceEntry();
        cse1.setDate("2021-06-01");
        cse1.setOdometer(5120.78F);
        long cse1Id = dao.insert(cse1);
        /* Add another Service Entry noting it's id */
        CarServiceEntry cse2 = cse1;
        cse2.setDate("2021-06-02");
        cse2.setOdometer(7065.83F);
        long cse2Id = dao.insert(cse2);

        /* Use a single Income to add 3 Income Entries for (linked to) the 1st Service Entry */
        Income incomeEntry = new Income();
        incomeEntry.setIncomeCarServiceEntryId(cse1Id);
        incomeEntry.setPaymentType(10);
        incomeEntry.setTotalAmount(120.00F);
        dao.insert(incomeEntry);
        incomeEntry.setPaymentType(20);
        incomeEntry.setTotalAmount(230.00F);
        dao.insert(incomeEntry);
        incomeEntry.setPaymentType(15);
        incomeEntry.setTotalAmount(75.55F);
        dao.insert(incomeEntry);

        /* Use the same Income Entry to add 1 Entry for the 2nd Service Entry */
        incomeEntry.setIncomeCarServiceEntryId(cse2Id);
        incomeEntry.setPaymentType(25);
        incomeEntry.setTotalAmount(134.56F);
        dao.insert(incomeEntry);

        /* Add some Expense Entries */
        Expense expenseEntry = new Expense();
        expenseEntry.setExpenseCarServiceEntryId(cse1Id);
        expenseEntry.setLocation("London");
        expenseEntry.setTotalCost(500.00F);
        dao.insert(expenseEntry);
        expenseEntry.setLocation("New York");
        expenseEntry.setTotalCost(60.66F);
        dao.insert(expenseEntry);
        expenseEntry.setExpenseCarServiceEntryId(cse2Id);
        expenseEntry.setLocation("Paris");
        dao.insert(expenseEntry);
        expenseEntry.setLocation("Hamburg");
        dao.insert(expenseEntry);
        expenseEntry.setLocation("Madrid");
        dao.insert(expenseEntry);

        dao.insert(new CarServiceEntry("2021-06-03",1765.34F));

        for (CarServiceEntryWithIncomeWithExpense cse: dao.getAllCarServiceEntriesWithIncomesAndWithExpenses()) {
            Log.d(
                    TAG,
                    "CSE ID = " + cse.carServiceEntry.getId() +
                            " Date = " + cse.carServiceEntry.getDate() +
                            " ODO = " + cse.carServiceEntry.getOdometer()
            );
            for (Income i: cse.incomeList) {
                Log.d(
                        TAG,
                        "\tIncome Payment Type is " + i.getPaymentType() + " Total is " + i.getTotalAmount()
                        );
            }
            for(Expense e: cse.expenseList) {
                Log.d(
                        TAG,
                        "\tExpense Location is " + e.getLocation() + " Total is " + e.getTotalCost()
                );
            }
        }
    }
}

Result

The following is output to the Log :-

2021-06-11 13:01:35.116 D/CSEINFO: CSE ID = 1 Date = 2021-06-01 ODO = 5120.78
2021-06-11 13:01:35.116 D/CSEINFO:  Income Payment Type is 10 Total is 120.0
2021-06-11 13:01:35.116 D/CSEINFO:  Income Payment Type is 20 Total is 230.0
2021-06-11 13:01:35.116 D/CSEINFO:  Income Payment Type is 15 Total is 75.55
2021-06-11 13:01:35.116 D/CSEINFO:  Expense Location is London Total is 500.0
2021-06-11 13:01:35.116 D/CSEINFO:  Expense Location is New York Total is 60.66
2021-06-11 13:01:35.116 D/CSEINFO: CSE ID = 2 Date = 2021-06-02 ODO = 7065.83
2021-06-11 13:01:35.117 D/CSEINFO:  Income Payment Type is 25 Total is 134.56
2021-06-11 13:01:35.117 D/CSEINFO:  Expense Location is Paris Total is 60.66
2021-06-11 13:01:35.117 D/CSEINFO:  Expense Location is Hamburg Total is 60.66
2021-06-11 13:01:35.117 D/CSEINFO:  Expense Location is Madrid Total is 60.66
2021-06-11 13:01:35.117 D/CSEINFO: CSE ID = 3 Date = 2021-06-03 ODO = 1765.34