Re: How to store/manage tags?
From: Lew Pitcher <lew.pitcher_at_digitalfreehold.ca>
Date: Sat, 13 Jul 2019 12:28:38 -0400
Message-ID: <qgd0rn$air$1_at_dont-email.me>
>
> Here, in restaruanttags, you don't need the id_restauranttag. As there
> should only be one combination of FK_id_restaurant, FK_id_tag per row
> (that is to say that no restaurant will have two or more identical tags),
> the FK_id_restaurant,FK_id_tag combination is sufficient to uniquely
> identify the row, thus
>
> CREATE TABLE restauranttag (
> id_restaurant ...,
> id_tag ...,
> FOREIGN KEY (id_restaurant) REFERENCES restaurants(id_restaurant),
> FOREIGN KEY (id_tag) REFERENCES tags(id_tag),
> PRIMARY KEY (id_restaurant,id_tag)
> ) ;
> should be sufficient.
>
> FWIW, if you add "ON DELETE CASCADE" to those two foreign keys, then mysql
> will delete the corresponding restauanttag row if you delete either the
> id_restaurant from the restaurants table (say, on a restaurant out-of-
> business), or the id_tag from the tags table (say, an obsolete tag). This
> makes maintenance of the restauranttag table easier, as it will have no
> "hanging" FK references.
>
>
>
Date: Sat, 13 Jul 2019 12:28:38 -0400
Message-ID: <qgd0rn$air$1_at_dont-email.me>
Lew Pitcher wrote:
> ^Bart wrote:
>
>> I need to store tags in a db, for example about restaurants, like >> romantic, sea view, etc., I thought to do something like this: >> >> restaurants >> --------------- >> id_restaurant >> name >> >> tags >> --------------- >> id_tag >> name
I presume that the column named "name" actually contains the text of the tag, as in "romantic" or "sea view".
>> >> restauranttags >> -------------- >> id_restauranttag >> FK_id_restaurant >> FK_id_tag
>
> Here, in restaruanttags, you don't need the id_restauranttag. As there
> should only be one combination of FK_id_restaurant, FK_id_tag per row
> (that is to say that no restaurant will have two or more identical tags),
> the FK_id_restaurant,FK_id_tag combination is sufficient to uniquely
> identify the row, thus
>
> CREATE TABLE restauranttag (
> id_restaurant ...,
> id_tag ...,
> FOREIGN KEY (id_restaurant) REFERENCES restaurants(id_restaurant),
> FOREIGN KEY (id_tag) REFERENCES tags(id_tag),
> PRIMARY KEY (id_restaurant,id_tag)
> ) ;
> should be sufficient.
>
> FWIW, if you add "ON DELETE CASCADE" to those two foreign keys, then mysql
> will delete the corresponding restauanttag row if you delete either the
> id_restaurant from the restaurants table (say, on a restaurant out-of-
> business), or the id_tag from the tags table (say, an obsolete tag). This
> makes maintenance of the restauranttag table easier, as it will have no
> "hanging" FK references.
>
>> Do you have other ideas? >> >> Regards. >> ^Bart
>
>
-- Lew Pitcher "In Skills, We Trust"Received on Sat Jul 13 2019 - 18:28:38 CEST