How to design the user information in the design of mysql database?

how to design the daily database user information section.

suppose the structure is as follows:

CREATE TABLE users(
    `id` INT(11) PRIMARY KEY AUTO_INCREMENT,
    `nickname` char(20) COMMENT "",
    `age` INT(4) COMMENT "",
    `phone` char(11) COMMENT "",
    `password` char(100) COMMENT "",
     ....
    `other` text COMMENT ""

)

the confusion lies in other . Other information about the user is stored in it. Structures such as {"birthday": "2018-06-06", "place": "Shaanxi"} .

what are the advantages and disadvantages of doing this? What a better design looks like. Thank you for your guidance!

Mar.21,2021

The data stored in

other should only be used for recording, and will never be used in the fields of search , sorting , statistics , and join tables .

I usually use one field to store fields such as birthday and place . These two fields can easily be used for statistical functions such as the proportion of users of different ages , the proportion of users in different regions , and the existence of other will be very inconvenient.

of course, how to store it still needs to be determined according to the actual demand.


I personally think this kind of design is quite good.
benefits of doing this:

  1. it's easy to extend fields, so you don't need to add fields in order to add attributes

disadvantages:

  1. it is not convenient to search for an attribute, for example, to search for a user's birthday within a certain period of time, then the birthday field may be recorded in other, and there may be attributes for other dates.
  2. is not good for index creation. The attribute in other is uncertain

specifically, I think we need to determine the information in the other according to the requirements, and we can't put all the other attributes in the other.

  • How to better design datasheets?

    problem description here are two data tables: order,user. Table relationship order.user_id Association user.id . Design a scene: in the background management, we need to search for orders, such as searching according to time and user name. ...

    Jul.28,2021
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1c1e684-333dd.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1c1e684-333dd.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?