I'm doing this database design stuff for a system where i need to store some variable length arrays into mysql database.

The length of the arrays will be (at most) in hundreds if not thousands.

New arrays will be created on a regular basis, maybe tens daily.

  1. should I store these arrays into one table that will soon grow gigantic or
  2. create a new table for each array and soon have a huge number or tables?
  3. something else? (like formatted text column for the array values)

to clarify, 1. means roughly

CREATE TABLE array (id INT, valuetype VARCHAR(64), ...)
CREATE TABLE arr_values (id INT, val DOUBLE, FK array_id)

and 2.

CREATE TABLE array (id INT, valuetype VARCHAR(64),...)
CREATE TABLE arr_values (id int, val DOUBLE, FK array_id) -- template table
CREATE TABLE arr1_values LIKE arr_values ...

The arr_values will be used as arrays that is queried by joining to a complete array. Any ideas on why some approach is better than other?

mysql sql database-design
Edit
Report