CODEWITHSUNDEEP
mysqldatabase
Atul
Atul

Reputation: 4320

Best practice to store profile data in MySQL

I am writing a server application. Many users would connect to this service. The application stores each user's profile data to MySQL DB. I am not expert in databases so asking this question here.

Profile has many fields. The most straight forward way is to have each column in DB table for each field in profile.

However I am not sure if this is the best practice. In future if there are more fields added to the profile, table schema has to be changed. Also, for so many fields in profile creating those many columns (Just to store profile data + there would be many other columns in the table for lots of other data) would clutter the table.

As an alternative I can think of storing all profile data in one column. But not sure how to do this. Especially being in single column delimiting fields would be challenging. Is there any way to create nested columns in MySQL?

Any other better alternatives?

Upvotes: 0

Views: 1270

Answers (2)

Maximilian Ast
Maximilian Ast

Reputation: 3499

Oracle supports nested tables: http://www.orafaq.com/wiki/NESTED_TABLE, but I don't recommend it because it would break the First Normal Form (1NF)

Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).

Upvotes: 1

A.D.
A.D.

Reputation: 1180

Having a multi column profile or a one column profile are both valid but it depends how you need to query your data.

If you need to query your profile information in the database side (ie with SQL) you should go for a multi column profile.

If you never will (I emphasize never on purpose), you could go for a single column profile. But in that case, that means you can never query the information, not even for a join on an other table.

My preference will go with the multi column because you never know what will be needed in the future.

Upvotes: 1

Related Questions