Database design: many rows and many tables?

Question

I am doing this material to create a database for a system where I need to store some arrays of variable length in a mysql database.

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

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

• Should I store these arrays in one table, which will soon become gigantic or
• create a new table for each array and will soon have a huge number or tables?
• something else? (e.g. formatted text column for array values)

for clarification, 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 ​​arguments will be used as arrays, which are requested by attaching to the full array. Any ideas on why some approach is better than others?

Answer 1

As with all answers to such questions, it always depends on what your final result should be, but personally, I would always prefer one table over dynamically created tables - this simplifies queries. It also makes it a little easier (I think) when you look at the database schema - many thousands of tables can make finding what you need when accessing the database a little easier.

In addition, if you find that you need to expand your “array” at some point with another field, this means that instead of a table, one database table will be displayed.

Answer 2

Many rows in multiple tables. Creating a new table for each new structure / record is absolutely wrong and the worst way to use a relational database.

In fact, almost every time your code dynamically creates tables, you do something terribly, terribly wrong.

Answer 3

It seems to me that every data array has a clear outline. Did you think you were using a NoSQL database? From my point of view, it will be much easier to work.

If you must adhere to MySQL, then you definitely want as few tables as possible. Based on what you presented, you can 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 "type" array, also add an instance column.

Answer 4

Increase the number of tables to normalize the scheme (very little or no duplication / duplication). Do not increase the number of tables outside of this and be careful when adding a new table for performance (modern databases, with a few exceptions, faster than you think), or for solving edge cases such as duplication that occurs once per application lifetime or for 1% of the rows.

It would also be easier to understand your question if we knew the subject matter - these addresses, clients, books, or what?