Re: How to store/manage tags?

^Bart wrote:

>> 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

> 
> Yes it's true, a restaurant could have just one time "sea view" and
> "romantic" but other restaurant could have same tags for this reason I
> created a tags table and I'll enable a search engine to filter them!

No, I haven't made myself clear...

You would hardly describe "Restaurant Bart" as "Romantic" and "Romantic", so the restauranttags table would only have /one/ entry consisting of   id_restaurant == "Restaurant Bart", and   id_tag == "Romantic".

Of course, "Restaurant Lew" (a different restaurant) could also be "Romantic", so the restauranttags table would also contain a single, unique, entry consisting of
  id_restaurant == "Restaurant Lew", and   id_tag == "Romantic".

That combination, ["Restaurant Lew", "Romantic"] is unique, and does not intrude on any other tag for "Restaurant Lew", nor on any other restaurant that has a "Romantic" tag. The same goes for ["Restaurant Bart","Romantic"].

For our two restaurants, the restauranttag table might look like...

  "Restaurant Bart","Romantic"
  "Restaurant Bart","Sea view"
  "Restaurant Bart","Seafood"
  "Restaurant Lew","Romantic"
  "Restaurant Lew","Rustic"
  "Restaurant Lew","Wildlife"

Thus, the /combination/ of id_restaurant and id_tag results in a unique key. And, so, you do not need an additional id_restauranttag to provide a key to row unqueness.

FWIW, this works. I've used the same layout, for the same purpose, in a successful recipe program.

>> 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.

> 
> It means three tables, like what I wrote in my original post, are needed
> but you removed id_restauranttag
> 

> 
> Good idea! :)
> 
> Regards.
> ^Bart

-- 
Lew Pitcher
"In Skills, We Trust"