The structure of the database. Join or not join.

Question

We are creating a database structure using the mySQL Workbench for a new application, and the number of connections required to compile a list of data increases dramatically as the many-to-many relationship grows.

The application will be readable enough and have a couple of hundred thousand rows per table.

Questions:

• Is it really so difficult to merge tables where necessary, and thereby reduce joins?

• Should we start to look at horizontal splits? (combined with merge tables)

• Is there a better way to collapse tables to take care of many-to-many relationships?

• We discussed the issue of storing all the data in columns of serialized text and an application that does sorting instead of the database, but this seems like a very bad idea, although the database will be heavily cached. What do you think?

Answer 1

Go to the normalized database form. For most of the tasks, you don’t need more than 3 or 4 pieces, and you can still write representations for the most common associations. Denormalization will make you always think about updating fields in several places / tables when changing one property and will undoubtedly lead to more problems than advantages.

, - , . , .

Answer 2

:

• . . saynig " " , , .

• .

• . OLTP ( ) - . OLAP ( ) - , . - . , ... , OLTP, OLAP ( mySQL).

Answer 3

. , . , ?

Answer 4

, , . , , , ( ).

, , , , .

, ...

Answer 5

( , ?) , 10-15 . . , .

, .

, , .

, , , , , - .

Answer 6

, - , . , , .

, , , , - - .

, , , , .

, , . , , .

, , , .