CODEWITHSUNDEEP
mysqlsqldatabasedata-structuresdatabase-design
mdc
mdc

Reputation: 119

Storing structured user data in members table column/s

I wanted to ask for some advice in structuring the SQL database I am creating.

UPDATE: Storing Data in MySQL as JSON seems to clearly indicate that storing JSON in MySQL is not a smart choice.

My initial idea was to create a table for each user named '{user_id}' with the following rows:

  1. Datetime
  2. Entry (one-digit int)
  3. Comment (reasonably short string)
  4. Activity (one word)

However, I have read that creating a table for each user is not advisable because it's unmanageable in the long run.

Specifically, I wanted to know how I could put all the information that I would have put in the '{user_id}' table in the user's row of my 'members' table.

I had a few ideas, but don't know how good they are:

Are there any other better ways, or better data storage types than JSON objects?

What would be a good way of storing this information? Isn't handling the arrays/dictionaries going to become unmanageable over time when they become very big?

(one thing to keep in mind is that the 'data' entries would have to be daily modified and easily accessed)

Upvotes: 1

Views: 112

Answers (1)

jjc
jjc

Reputation: 310

I think you may simply want a single additional table, maybe called "activities" with a foreign key "user" to the "members" table. Then for each row in each of the per user table that you were originally thinking of, you have a row in the activities table with the value of "user" being the user in question. Since each row is of relatively small bounded size, one would expect the database to handle it well, and efficiency issues can be addressed by indexing. Basically I am agreeing with @MikeNakis

Upvotes: 1

Related Questions