How can I use MySQL arrays or something like that?

Question

So, for example, I have two tables, named users and folders. Any user can create folder and add co-authors to this folder.

How can I build my db to get all co-authors of a folder?

I want to use array, but Internet search shows that SQL does not have arrays, so I don't know what to do.

How can I have an array of all co-authors of a folder?

Answer 1

In SQL, you don't store an array, you store a set of rows.

You have a many-to-many relationship. Many users can be co-authors of a given folder, and presumably a user can be a co-author on many folders.

In a normalized relational database, the way to represent a many-to-many relationship is with a third table:

CREATE TABLE folderauthors (
  user_id INT NOT NULL,
  folder_id INT NOT NULL,
  PRIMARY KEY (user_id, folder_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
  FOREIGN KEY (folder_id) REFERENCES folders(folder_id)
);

Insert one row for each user/folder pairing. That is, if a given folder has three co-authors, then there will be three rows in the table above for that folder.

Then you can easily get the set of co-authors for a given folder:

SELECT user_id FROM folderauthors WHERE folder_id = ?

By storing it this way, it's also easy to do the complementary operation, to get the set of folders for a given author:

SELECT folder_id FROM folderauthors WHERE author_id = ?

As well as making other operations easy, like adding an author to a folder, removing an author from a folder, counting the authors per folder, etc.