When I'm building large users databases loaded with different types of data, I'm always having an issue with choosing the right approach.
Some users having standard 'name','surname','email' and others complete set of rows including 'cpf' (social security number in Brazil),'hair_color','last_login_at','eye_color' etc.
I'm usually picking between:
custom json field for less common rowsWhat would be your suggestion?
You could definitely flavor it with some NoSQL databases, particularly from Document DB subset.
Possible On-premise solutions: MongoDB, CouchDB or CouchBase
Possible Cloud solutions: Amazon DynamoDB, Microsoft Azure DocumentDB or Google Cloud Datastore.
Assuming I have to use conservative MySql, it might be reasonable to use JSON columns - right?
It depends on what app you are building. If you don't need to make DB query according less common data(i.e. get all users, where hair_color is brown), then json column, or event text column with serialized data would be appropriate. Otherwise, to make such queries might be painfull, and better option would bė no 3
JSON column support indexing (https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html#json-column-indirect-index) in case I need to search by hair_color more often.
If its 1:1, i.e. every login user will have a profile, then I don't see much disadvantage in having a very wide table rather than two or more tables.
If your design is rapidly changing, you might prefer the flexibility of several smaller tables (e.g. less of an impact when the boss decides every user should store their favourite food).
Once it's not one to one (e.g., user might have two addresses) then that should be a separate entity.
You might also need to consider classification of data. For instance you may prefer to put all PII data in a separate table so that it can be encrypted with the User table just used for logins?
Please or to participate in this conversation.