Re: The best way to insert tags in a DB

From: Lew Pitcher <lew.pitcher_at_digitalfreehold.ca>
Date: Sun, 20 Oct 2019 09:57:27 -0400
Message-ID: <qohp4a$8t0$1_at_dont-email.me>

^Bart wrote:

> Hi everybody! :)
>
> I need to insert in a db tags about food recipes and I thought to start
> in this way:
>
> recipes
> -------------------------
> id_recipe name
> 1 hamburger
>
> recipetags
> -------------------------
> id_recipetag name
> 1 meat
> 2 bread
> 3 latuce
>
> recipedetails
> ------------------------------------------------
> id_recipedetail FK_id_recipe FK_id_recipetag
> 1 1 1
> 2 1 2
> 3 1 3

This one is better than your alternative below (but, see my alternative at the bottom of this post)

> Is there a better way to insert tags? I thought I could insert them like
> this:
>
> recipetags
> ------------------------------------
> id_recipetag name
> 1 meat
> 2 bread
> 3 lettuce
>
> recipes
> -----------------------------------------------------
> id_recipe name tags
> 1 hamburger meat, bread, lettuce
>
> What do you think about these two ideas? Do you have another one?

Here's how I solved this problem...

CREATE TABLE recipe		(
  recipe_id		INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

  ...
) ;

CREATE TABLE tag		(
  tag_id		SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tag_string	VARCHAR(20) NOT NULL,

  ...
) ;

CREATE TABLE recipe_tag	(
  rt_recipe		INTEGER UNSIGNED NOT NULL,
  rt_tag		SMALLINT UNSIGNED NOT NULL,
  FOREIGN KEY (rt_recipe) REFERENCES recipe(recipe_id),

  FOREIGN KEY (rt_tag) REFERENCES tag(tag_id),   PRIMARY KEY (rt_recipe,rt_tag)
) ;

[Quoted] Table 'recipe' contains recipes, one recipe per row. Each row has a unique identifier value called 'recipe_id'.

Table 'tag' contains the tags, one tag per row. Each row has a unique identifier value called 'tag_id'.

[Quoted] Table 'recipe_tag' contains a list of tags per recipe, one row for each unique combination of recipe and tag. This table contains only 'recipe_id' and 'tag_id' values, in pairs, with constraints to ensure that no recipe_id/tag_id pair is repeated.

-- 
Lew Pitcher
"In Skills, We Trust"

Received on Sun Oct 20 2019 - 15:57:27 CEST