Is there a data type in MySQL that is similar to a dynamic array in C or C++?

Question

I want to structure a MySQL table with many of the the usual columns such as index as an integer, name as a varchar, etc. The thing about this table is I want to include a column that has an unknown number of entries. I think the best way to do this (if possible) is to make one of the columns an array that can be changed as any entry in a database can. Supposing when the record is created it has 0 entries. Then later, I want to add 1 or more. Maybe sometime later still, I might want to remove 1 or more of these entries.
I know I could create the table with individual columns for each of these additions, but I may want as many as a hundred or more for one record. This seems very inefficient and very difficult to maintain. So the bottom-line question is can a column be defined as a dynamic array? If so, how? How can things be selectively added to or removed from it? I'll take a stab in the dark and guess maybe make a table contain another table. I've never heard of this because my experience with MySQL has been mostly casual. I make databases and dynamic websites because I want to.

Answer 1

The way to do this in a relational database is to create another table. One column of that table will have a foreign key pointing to the primary key of that table that should have had the array (or multiple columns, if the primary key consists of more than one row). Another column has the values that'd be found in the array. If order matters, a third column would store some other values indicating the ordinality.

Something along the lines of:

CREATE TABLE elbat_array
             (id integer,
              elbat integer -- or whatever type the primary key column has
                    NOT NULL,
              value text, -- or whatever type the values should have
              ordinality integer
                         NOT NULL, -- optional
              PRIMARY KEY (id),
              FOREIGN KEY (elbat)
                          REFERENCES elbat -- the other table
                                     (id) -- and its primary key column
                          ON DELETE CASCADE,
              UNIQUE (ordinality));

To add to the "array", insert rows into that table. To remove, delete rows. There can be as many as zero rows (i.e. "array" elements) or as much as there's disk space (unless you hit any limit of the DBMS before, but if such a limit applies it would be very large, so usually that should not be a problem).

Also worth a read in that context: "Is storing a delimited list in a database column really that bad?" While it's not about an array type in particular, on the meta level it discusses why the values in a column should be atomic. An array would violate that as well as a delimited list does.