I'm trying make database of recipes. In the table "recipes" with col "ingredients", I would like to have a list of ingredient IDs, e.g. [2,5,7]. Can I make something like this or I should be looking for another solution?

import sqlite3

conn = sqlite3.connect('recipes.db')
c = conn.cursor()


c.execute('''CREATE TABLE recipes(ID INT, name TEXT, ingredients INT)''')
c.execute('''CREATE TABLE ingredients(ID INT, nazwa TEXT, kcal REAL)''')

Another idea is to make another table (The list of ingredients) where I will have 15 cols with number of ingredients.

c.execute('''CREATE TABLE The_list_of_ingredients(ID INT, ingredient1   INT,    ingredient2 INT,    ...)''')

Can I connect every ingredient 1, ingredient 2 ... with their respective ingredients ID?

CodePudding user response：

You're likely looking for a many-to-many relation between recipes and their ingredients.

CREATE TABLE recipes(ID INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE ingredients(ID INTEGER PRIMARY KEY, name TEXT, kcal REAL);
CREATE TABLE recipe_ingredients(
  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  recipe_id INTEGER,
  ingredient_id INTEGER,
  quantity REAL,
  FOREIGN KEY(recipe_id) REFERENCES recipes(ID),
  FOREIGN KEY(ingredient_id) REFERENCES ingredients(ID)
);

This way your data might look something like e.g.

ingredients

recipes

recipe_ingredients

(assuming kcal is kcal per 100g, and quantity is in grams and a rather creamy omelette)

CodePudding user response：

You can try to store id's as string

json.dumps(list_of_ingredients_ids)

But probably best solution is many to many relation