What is the best way to store data in a database?

Question

MySQL

I have a members table with many fields.
Today I decided to make one function for my forum - additional profile fields.
Before me there was a question: how to store the data?
Option 1:
Create a separate table and use LEFT JOIN to display additional data.
Option 2:
Create fields in an existing members table

Which is better (less heavy on the database)?

Answer 1

The questions and answers are amazing. Especially about "I would interview customers." Wow!..
en.wikipedia.org/wiki/%D0%9D%D0%BE%D1%80%D0%BC%D0%B0%D0%BB%D1%8C%D0%BD%D0%B0 %D1%8F_%D1%84%D0%BE%D1%80%D0%BC%D0%B0 - read this.

Answer 2

In your particular case, the first solution with joins would be the best option. This method of optimizing the database has long been known, but on my own behalf I still want to add that you should not go to extremes and produce a bunch of joins - one will be enough . Those. creating a separate table for each field (as, for example, done in Drupal), on the contrary, will slow down the database.
PS. There is another option to make one column in the table and store the field house in it as json, but in your case this method is unlikely to be justified.

Answer 3

When developing the database structure, I try to adhere to the philosophical "no UPDATEs - only INSERTs". This makes the base much more flexible. But the number and complexity (JOINs) of SELECTs increases accordingly. If there is a performance margin, then of course "option 1".

Answer 4

I would ask clients what fields they would like to add. The most popular ones would be added to the main table, and the “arbitrary fields” feature would be thrown into the far TODO list and forgotten.

Answer 5

Option 2 - if you need to add fields often, then keep in mind that adding a field is actually creating a second copy of the table (that is, for a long time). Also, with a large number of fields, the volume of the table grows, and the access time worsens.
As for option 1, consider also the option to select records without JOIN, and 2 queries - MySQL on joins may work inefficiently (unfortunately, I can’t say more precisely, this must be checked in practice).
I would personally do option 1, but with caching in some kind of key-value storage.

Answer 6

The second table contains:
id | member_id | field_name | value

Answer 7

shapershifter08 is just about such a grill option

Answer 8

Thanks all for the replies.