Re: How to store/manage tags?

^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
>
> restauranttags
> --------------
> id_restauranttag
> FK_id_restaurant
> FK_id_tag

[Quoted] [Quoted] Here, in restaruanttags, you don't need the id_restauranttag. As there [Quoted] [Quoted] 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 [Quoted] FK_id_restaurant,FK_id_tag combination is sufficient to uniquely identify [Quoted] the row, thus

  CREATE TABLE restauranttag (
    id_restaurant ...,
[Quoted]     id_tag ...,

    FOREIGN KEY (id_restaurant) REFERENCES restaurants(id_restaurant),
    FOREIGN KEY (id_tag) REFERENCES tags(id_tag),
    PRIMARY KEY (id_restaurant,id_tag)

[Quoted] [Quoted] FWIW, if you add "ON DELETE CASCADE" to those two foreign keys, then mysql [Quoted] [Quoted] 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"