Database design for Tagging multiple types of entities

Question

I'm currently designing a database schema that's used to store recipes. In this database there are different types of entities that I want to be able to tag (ingredients, recipe issuers, recipes, etc). So a tag has multiple n:m relations. If I use the "three table design", this would result in tables (cross table) for every entity type (recipes, ingredients, issuers) that I have. In other words every time I introduce an entity I have to add a cross table for it.

I was thinking of creating one table which has a unique id, that all the entities refer to, and a n:m relation between the tags table and the "unique id"-table. This way there is just one cross table between the "unique id"-table and the tag table.

Just in case that some people will think this question already was asked. I already read Database Design for Tagging. And there the three table design is mentioned.

Answer 1

I would say it depends on how you want to use the tags.

I would imagine you could create an additional intersection table for each entity type you want to tag, if you only search one type of entity at a time. In other words, it would be normal to say, "show me the ingredients with tag 'yummy'" but it's not clear what it would mean to say, "show me both ingredients and recipe issuers with tag 'yummy.'" In this case, having a separate intersection table per entity is fine.

But if you do need to search for all entities of all types with a given tag, then using the single "ID" table is easier. Make all the entity tables point to it with a column that you define as both a primary key and a foreign key:

CREATE TABLE Recipes (
  recipe_id INT NOT NULL PRIMARY KEY, -- not auto-generated
  FOREIGN KEY (recipe_id) REFERENCES Taggables(id)
);

The only weakness of this plan is that you can't prevent a row in both Recipes and Ingredients from pointing to the same row in Taggables.

INSERT INTO Taggables (id) VALUES (327);
INSERT INTO Recipes (recipe_id, name) VALUES (327, 'Hollandaise sauce');
INSERT INTO Ingredients (ingr_id, name) VALUES (327, 'eggs');

Do you want every tag associated with eggs to also apply to Hollandaise sauce?

I'm just pointing out this aspect of the single-table design. It may still be the best way to model your tagging, given other requirements. But you should be watchful of the potential for collision of id's in the dependent tables.