How to add references to multiple "tags"?

Question

In my database, I have a table for "tags". So, in my main table, I want to give each item multiple tags.

For example, if my main table is dog, I want to add tags for small brown and mean. But in my tags table, I might have 50 possible tags. Each dog can have as many tags if I want.

How do I do that?

I don't really want to create one column for each tag in the main table with a boolean. Is there a way to specify multiple tags in one field?

(I'm rather not used to working with databases, so this probably sounds stupid.)

In the end, the goal is to be able to get all the dogs that match a particular tag.

Answer 1

You are describing a many-to-many relationship between dogs and tags. You would typically represent that with a bridge table, that references the two other referential tables, that store dogs and tags.

Assuming the following structures for the referential tables:

create table dogs (
    dog_id int primary key,
    name text
);

create table tags (
    tag_id int primary key,
    name text
);

You would create the bridge table as:

create table dogs_tags (
    dog_id int references dogs(dog_id),
    tag_id int references tags(tag_id),
    primary key (dog_id, tag_id)
);