Database design for tags of several types of objects

Question

I am currently developing a database schema used to store recipes. There are different types of objects in this database that I want to be able to mark (ingredients, recipes, recipes, etc.). Thus, a tag has several n: m relationships. If I use the “three-table design”, this will lead to the tables (crosstab) for each type of entity (recipes, ingredients, issuers) that I have. In other words, every time I entered an entity, I have to add a crosstab for it.

I was thinking of creating a single table with a unique identifier, to which all entities belong, and the n: m relationship between the tag table and the "unique identifier" -table. Thus, there is only one cross-reference table between the "unique identifier" and the tag table.

Just in case, some people will think that this question has already been asked. I already read Database Design for tags . And the design of the three tables is mentioned there.

Answer 1

I do not see anything wrong with the fact that I have one table for all tag assignments (unlike several tables - one for each object marked with a tag).

: -

- - - - - - - - - -
Tag
    ID           // PK
    Name
    ...

- - - - - - - - - -
Taggable
    ID           // PK
    ...

- - - - - - - - - -
TagAssignment
    Tag_ID       // FK -> Tag.ID
    Taggable_ID  // FK -> Taggable.ID
    ...

- - - - - - - - - -
EntityOne
    Taggable_ID  // FK -> Taggable.ID
    ...

- - - - - - - - - -
EntityTwo
    Taggable_ID  // FK -> Taggable.ID
    ...

EntityOne.TaggableID EntityTwo.TaggableID EntityOne EntityTwo?

:

- - - - - - - - - -
EntityOne
    ID           // PK
    Taggable_ID  // FK -> Taggable.ID (Nullable)
    ...

- - - - - - - - - -
EntityTwo
    ID           // PK
    Taggable_ID  // FK -> Taggable.ID (Nullable)
    ...

, Taggable, , , , . , , " " ( , Taggable), "" .

NB. - , . , , , .: -)

---

:

: Taggable. :

• , . , Taggable " " , Taggable_ID FKs PKs . , , , , "taggable", "" ( , ..) "":-)? "", Taggable?

• , DB taggable-one-entity... AFAIK, , FKs PK: "taggables ( ).

- :

- - - - - - - - - -
Taggable
    ID           // PK
    Type        
    ... 
    - - - - - - - -
    Constraint: (ID, Type) is unique


- - - - - - - - - -
EntityOne
    ID
    Taggable_ID   
    Taggable_Type // Constraint: always = 'EntityOne'
    ...
    - - - - - - - -
    FK: (Taggable_ID, Taggable_Type) -> (Taggable.ID, Taggable.Type)

, , taggables. , , , , .

Answer 2

, , .

, , , . , : " " yummy ", , ," , - ". , , .

, "ID". , , :

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

, Recipes Ingredients 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');

, , , ?

. , . .

Answer 3

, . , . entity witch, ( ). , ,

• EntityId

• EntityId

RecipeIssuer

• EntityId
• SomeOtherInformation

.

Answer 4

?

(PK: , set_id [, TypeDesc])

(PK: (set_id, FK: ), )

PS: /

Answer 5

, ..

: Id, Type, Tag

, "" ().

Answer 6

"" . , , (: , : , ).

- (I) (A). , . - , -, - , . . , , , , , . , , factory, , . , .

, A N: M.

. , , , , , , . , make, model, year, engine .., , , - , , . , , . , , , - . _ , , - , , .

, , .

- . . , : A.

-. , , " nissan". , , : Nissan, : . , , , .

, , , .

(I), (A) (T) N: M: I2A . T2I . T2A .

6 , , , , : = , = Nissan

What I want to have is something like www.summitracing.com. Select "Departments" at the bottom left "Shop", select any category, and you will see how they managed to assign objects to some properties. They have an engine size for most applications, but they also have a width property when searching for drives.

Any feedback on this would be greatly appreciated; I will start to embed dead, trying to develop this.