What is the most correct approach for nested database in Room?-CodePudding

I'm considering creating a schema with both one-to-many and many-to-many elements. As I am a bit of a novice in this matter, I searched some resources and came across two types of examples; 1. It puts the content of the lower layer object into the upper layer and then processes it. The second type is to create a foreign key, which one do you think is more correct?

@Entity
public class User {
   @PrimaryKey
   public final int id;
   public final String login;
   public final String avatarUrl;

}

@Entity
public class Tweet {
   @PrimaryKey
   public long id;
   public String body;
   public String createdAt;
   public long userId;

   // this field will be ignored by Room, but still can be used in other places in the Twitter app
   @Ignore 
   public User user;
}


@Entity
public class User {
    @PrimaryKey
    public final int id;
    public final String login;
    public final String avatarUrl;
}


@Entity(foreignKeys = @ForeignKey(entity = User.class,
                                  parentColumns = "id",
                                  childColumns = "userId",
                                  onDelete = CASCADE))
public class Tweet {
    @PrimaryKey
    public long id;
    public String body;
    public String createdAt;
    public long userId;
}

Which of these two options is correct and performant?

Also, this is the Database schema that i want to create.. what do you think i should do.. please show examples in java i don't know kotlin


{
 "UserDatabase":
 [
   {
     "sql_id": 0,
     "name": "Test Name",
     "username": "null",
     "gender": "male",
     "profile_photo_path": "path",
     "pro_version": true,
     "date_of_birth": "01/01/0000",
     "weight": 100,
     "height": 186,
     "waist_size": 0,
     "neck_size": 0,
     "hip_size": 0,
     "bmr_formula": "Benedict",
     "macro_formula": "Fitness",
     "starting_date": "18/02/2022",
     "saved_items":
     [
       {
         "sql_id": 0,
         "parent_id": 0,
         "item_id": 0,
         "item_name": "name",
         "item_image": "item image",
         "item_url": "url",
         "item_editor": "editor"
       }
     ],
     "days":
     [
       {
         "sql_id": 0,
         "parent_id": 0,
         "id": 0,
         "date": "16/02/2022",
         "weight": 96,
         "steps": 25252525,
         "taken_kcal": 257.56,
         "burned_kcal": 27.86,
         "carb": 25.52,
         "protein": 5.2,
         "fat": 8.4,
         "water_ml": "3250",
         "water_percent": 27.68,
         "daily_water_list":
         [
           {
             "sql_id": 0,
             "parent_id": 0,
             "id": 0,
             "time": 1644999314504,
             "water_ml": 300
           }
         ],
         "daily_meals":
         [
           {
             "sql_id": 0,
             "parent_id": 0,
             "id": 0,
             "food_meal_time": "Meal Time",
             "food_name": "Name",
             "food_image": "image url",
             "food_kcal": 300.56,
             "food_portion": "Portion"
           }
         ]
       }
     ]
   }
 ]
}

CodePudding user response：

The better way is to use Foreign Keys as this then enforces referential integrity.

Otherwise there is little difference with the exception of the inclusion of the parent (User) in the child(ren) (Tweet). This is not needed (as you have the reference to the parent) and it contradicts normalisation (i.e. it duplicates data).

More specifically

Adding Foreign Keys introduces constraints (rules) that say that the value in the child table (tweet) that references the parent table (users) must be a value that exists in the parent table otherwise a conflict occurs.

The onDelete action of CASCADE will automatically delete children should the parent be deleted, thus assists with/simplifies maintaining the referential integrity.