SQL column containing array of table row values

Question

I'm quite new to databases and SQL. I have an assignment, but I don't know how to solve this.

Let's say I have table named Persons. It has following columns: userId (primary key), userName and tags.

Tags is another table, containing only id and tagName. Person can have multiple tags, for example: "football", "cooking" etc.

Each tag corresponds to Tag table row. But I'm not sure how to achieve this kind of relation. How can I achieve this?

I'm guessing it has to something to do with foreign keys and making persons tag column an array of foreign keys.

Answer 1

You normally want to have a relation table (this is called a many-to-many relationship, or M:M).

Persons(id int, name varchar(40)...)

Tags(id int, name varchar(40)....)

PersonTags(id int,
           personid FOREIGN KEY References Persons(id), 
           TagId FOREIGN KEY references Tags(id))

This way you can have as many or as few tags as you like, but the tag data isn't stored in-row for the person. You could potentially store a series of values in a single field, but this is a really terrible idea for a variety of reasons.