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.

JNK · Accepted Answer · 2012-02-20 13:11:29Z

9

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.

answered Feb 20, 2012 at 13:11

JNK

65.5k15 gold badges127 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Diego Over a year ago

just complementing @JNK's answer. you can create your foreign key to PERSONS with ON DELETE CASCADE option, which means that when a person is deleted, all tags belonging to it (the record on PersonTags), will be deleted too, but nothing will happen to the TAG itself on the Tags table

tim-phillips Over a year ago

What are the reasons that this is a terrible idea?

Collectives™ on Stack Overflow

SQL column containing array of table row values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related