How to set up databases (tables) in Android to perform searches between them

Question

EDIT

I have learned that I might not need multiple databases, but multiple tables within that database.

What I have now I am building an app that is going to deal with recipes. Currently, I have sort of a locker where you can add your items that you want in that locker. That could be, for example, a pack of minced beef or a loaf of bread or what not. I let the user add this in the app, depending on what they have in their locker/fridge/freezer. I am doing this, using SQLiteDatabase. Everything works the way I want it do, but now, I want to add some more features.

What I want to achieve

Okay, so in my app, I have a search button. When a user wants to search, he should be able to type an ingredient (like minced beef) and then receive results of recipes containing minced beef. He should also be able to search for a recipe and then get to that recipe, or recipes similar to the one he typed in.

Scenario 1. A user searches for an ingredient.

What should happen The user should get results on all recipes containg that special ingredient. When the user finds a recipe that he likes, he should be able to click on it and the app should show what ingredients he have at home and what ingredients he needs to purchase.

Scenario 2. A user searches or a recipe

What should happen. The user gets result on the recipes that matches his search. If he clicks on one, the ingredients that he has should show (as in Scenario 1) and the ones he needs to purchase should also show, also as in scenario 1.

The question

How should I, as efficiently (in terms of execution time) as possible set up the databases to be able to display information from different databases with one search. I found this http://www.databaseanswers.org/data_models/recipes/index.htm, from what I'm guessing that I will need multiple databases. I do not need help to code the setting up phase, but merely how the structure of databases should be designed.

Thanks in advance.

Answer 1

You don’t need to have multiple databases to store objects of your application model. You may want to consider using the following (or something similar) domain model classes for your app and have the corresponding tables in the database:

public class Recipe {
    int id;
    String name;
    String source; // Mom, A friend's name, A website url, a magazine name, etc.
    Date lastUpdated; // When the recipe was added/updated
    ArrayList<Ingredient> ingredients; // A list of ingredients used by this recipe 
}

public class Ingredient {
    int id;
    String name;
    String preferredSources; // a store name, a friend's name :), etc.
    ArrayList<Recipe> recipes; // A list of recipes which use this ingredient
}

public class RecipeIngredient { // Join class for many-to-many relationship
   int recipeId;
   int ingredientId;
}

public class Inventory {
    int ingredientId;
    int location; // locker, fridge, freezer, etc.
    float quantity; // in weight, count, etc.
    Date lastUpdated; // When the last replenishment was done
}

Since you have a many-to-many relationship between Recipe and Ingredient objects, I am suggesting creating a join class RecipeIngredient. The Inventory class is for the ingredients you currently have at home.

Your searches may happen against Recipe and Ingredient classes (tables). Once you get the corresponding ingredientId(s), you may search your Inventory for the availability of those ingredients at home.

After shopping for Ingredient items, you should update the Inventory class (table). Also, after an ingredient is used up, you should update its Inventory quantity.

To simplify the development effort including automatic database schema (tables) creation for your app, you may consider using an ORM like JDXA. Here are some code snippets for handling many-to-many relationships with JDXA.