Database design: lots of rows vs lots of tables?

Question

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?

Answer 1

Lots of rows in few tables. Making a new table for each new structure/record is absolutely incorrect and the very worst way to use a relational database.

In fact, almost any time your code is dynamically creating tables, you are doing something terribly, terribly wrong.

Answer 2

As with all answers to these sort of questions, it always depends somewhat on what your end result needs to be, but personally, I would always favour a single table over dynamically created tables - it makes for much simpler querying. It also makes it somewhat simpler (I think) when you look at the database schema - many thousands of tables may make finding what you need when accessing the database directly a bit easier.

Additionally, if you find you need to extend your 'array' at some point with another field, it means that there will be a single database table to alter, rather than many.

Answer 3

It looks to me like each array of data has a distinct schema. Have you considered using a NoSQL database? It will be much easier to work with, in my opinion.

If you must stick with MySQL, then you definitely want as few tables as possible. Based on what you've presented, you could have one table with three columns -

array ;connects all the related records to the correct array
field ;the name of the field (array key)
value ;the actual value for that field

And, if you need multiple copies of the same array "type", add an instance column as well.