How to create multiple tables of the same entity on Room Database?

Issue

The problem

Suppose you have multiple gardens each with a different number of plants. Your job is check every plant of each garden from time to time. Every visit you've to annotate some attributes of the plant, like if its well watered and its height. The app is meant to help during these visits.

My approach

I'd like to use Room Database. So I created an entity GardenVisit that have its unique id and the date of the visit. Then I'd need a GardenAnnotation entity. This entity would have a row for every plant of the garden with its id and the traits annotated on the visited day. I thought about creating an table for every GardenVisit and link them with a one-to-one relationship, but I couldn't find a way to do this.

Why I want to create a GardenAnnotation table for every GardenVisit?

In the app you can delete a garden visit. So, when deleting it, it should delete its GardenAnnotation table as well. This seemed the easiest way to have this feature.

Conclusion

How can I create multiple tables of the same entity in Room Database and link them with another table?

If you have a better approach, I'd appreciate if you could share it. It feels weird to create a lot of tables of the same entity, actually.

Solution

Multiple tables for the sake of splitting up what is basically the same layout (schema) probably makes little sense and will likely complicate matters.

From your description you have some common things:-

• Gardens.
• Plants.
• Visits
• Traits.
• Annotations (findings/traits per visit).

I'd suggest tables accordingly.

A Garden table that likely has but may not be limited to a human identifier of the garden (Kew, Hanging Gardens of Babylon .... ) an (as it will already exist and is efficient) identifier (id).

A Plant table (dandelion, rose ....) with columns for id, name and perhaps other info about the plant.

A table (not mentioned) that maps/links/associates a plant to a garden, allowing a many to many relationship (a gardens can have many plants, a plant can be used in many gardens). 2 Columns one for the map to the Garden the other to the Plant.

A Visit table that has the date/time of the visit perhaps start/end and a map/link.... to the garden.

A Trait table e.g. well watered, dead (if I'm tending the plant) .... Columns would be id and trait (the exact requirements)

An Annotation table that will link to the visit (and therefore garden) and link to the plant within the garden and a link to the trait(s) to be assigned.

So the schema could be based upon the SQLite (to demonstrate how the database/relationships work from an SQLite pov) :-

DROP TABLE IF EXISTS annotation;
DROP TABLE IF EXISTS trait;
DROP TABLE IF EXISTS visit;
DROP TABLE IF EXISTS garden_plant_map;
DROP TABLE IF EXISTS garden;
DROP TABLE IF EXISTS plant;

CREATE TABLE IF NOT EXISTS garden (garden_id INTEGER PRIMARY KEY, garden_name TEXT UNIQUE);
INSERT INTO garden (garden_name) 
    VALUES('Kew' /* id will be 1 */),('Hanging Gardens of Babylon' /* id will be 2 and so on (probably)*/)
;

CREATE TABLE IF NOT EXISTS plant(plant_id INTEGER PRIMARY KEY, plant_name TEXT UNIQUE);
INSERT INTO plant (plant_name) 
    VALUES('Rose' /* id will be 1 etc*/),('Dandelion'),('Poppy'),('Azelia'),('Oak'),('Beech')
;
CREATE TABLE IF NOT EXISTS garden_plant_map (
    garden_map INTEGER,
    plant_map INTEGER,
    PRIMARY KEY (garden_map,plant_map)
    FOREIGN KEY (garden_map) REFERENCES garden(garden_id) ON DELETE CASCADE ON UPDATE CASCADE
    FOREIGN KEY (plant_map) REFERENCES plant(plant_id) ON DELETE CASCADE ON UPDATE CASCADE
);
INSERT INTO garden_plant_map
    VALUES
        (1 /* Kew */, 3 /* Poppy*/),
        (1 /* Kew */, 1 /* Rose */),
        (2 /* Babylon */, 2 /* Dandelion */),
        (2,5),(2,6) /*Oak and Beech for Babylon */
;
CREATE TABLE IF NOT EXISTS trait (trait_id INTEGER PRIMARY KEY, trait_description UNIQUE);
INSERT INTO trait (trait_description)
    VALUES ('Well watered'),('Dead'),('Stressed'),('Flourishing'),('under watered')
;
CREATE TABLE IF NOT EXISTS visit (
    visit_id INTEGER PRIMARY KEY, 
    garden_map INTEGER,
    start_of_visit TEXT /* will be date in yyyy-mm-dd hh:mm:ss format*/,
    end_of_visit TEXT,
    visit_done INTEGER, /* 0/false or 1 (or greater)/true */
    FOREIGN KEY (garden_map) REFERENCES garden(garden_id) ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO visit (garden_map,start_of_visit,end_of_visit,visit_done)
    VALUES 
        (1,'2020-01-01 08:00','2021-01-01 10:00',true)
        ,(1,'2021-01-01 08:00','2021-01-01 10:00',false)
        ,(2,'2021-02-01 08:00','2021-02-01 10:00',false)
        ,(1,'2021-03-01 08:00','2021-03-01 10:00',false)
        ,(2,'2021-04-01 08:00','2021-04-01 10:00',false)
;

CREATE TABLE IF NOT EXISTS annotation (
    annotation_id INTEGER PRIMARY KEY,
    visit_map INTEGER REFERENCES visit(visit_id) ON DELETE CASCADE ON UPDATE CASCADE,
    trait_map INTEGER REFERENCES trait(trait_id) ON DELETE CASCADE ON UPDATE CASCADE,
    garden_plant_map_garden_map INTEGER, garden_plant_map_plant_map INTEGER,
    FOREIGN KEY (garden_plant_map_garden_map,garden_plant_map_plant_map) REFERENCES garden_plant_map(garden_map,plant_map)
);

INSERT INTO annotation (visit_map, trait_map, garden_plant_map_garden_map, garden_plant_map_plant_map ) 
    VALUES
        (1 /* visit on 1/1/20 */, 1 /* Well watered */, 1 /* Kew */, 3 /* Poppy */ )
        , (1 /* visit on 1/1/20 */, 5 /* under watered */, 1 /* Kew */, 1 /* Rose */ )
        
        , (3 /* visit on 1/2/21 */, 2 /* dead */, 2 /* Babylon */, 2 /* Dandelion */ )
        , (3 /* visit on 1/2/21 */, 4 /* flourishing */, 2 /* babylon */, 6 /* Beech */ )
        , (3 /* visit on 1/2/21 */, 3 /* stressed */, 2 /* babylon */, 5 /* Beech */ )      
;

SELECT 
    garden_name,
    start_of_visit,end_of_visit, visit_done,
    plant.plant_name,
    trait.trait_description,
        CASE WHEN visit_done THEN 'Completed' ELSE 'ToDO' END AS status
FROM annotation
JOIN visit ON visit.visit_id = annotation.visit_map
JOIN garden ON visit.garden_map = garden.garden_id
JOIN plant ON garden_plant_map_plant_map = plant_id
JOIN trait ON trait_map = trait_id
;

The result of the query being :-

And Lets say the visit with an id of 1 is deleted (although you could perhaps consider the visit_done being true as effectively deleting (so you could always go back in time)) e.g. using :-

DELETE FROM visit WHERE visit_id = 3;

Then the same query returns :-

i.e. the three annotations for visit 3 have been removed

Ignoring the deletion i.e. with the visit with a visit_id of 3 remaining then the tables look like :-

garden

plant

trait

visit

garden_plant_map

annotation

Answered By - MikeT