1

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:

  • Storing the user data as a JSON object (converted to a string) in an additional column 'data' of the 'members' table. Would that become unmanageable in the long run too (due to JSON object string becoming too long)?

  • Storing the user data in various additional columns of the 'members' table, maybe one for each of the parameters listed above (each of them being an array)

  • Storing the user data in various additional columns of the 'members' table, maybe one for each of the parameters listed above (each of them being a dictionary or some other data structure)

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)

Dharman
  • 30,962
  • 25
  • 85
  • 135
mdc
  • 119
  • 1
  • 7
  • It sounds like you might need a primer on MySQL - I'd suggest reading up on basic MySQL table design (http://opensourceforu.efytimes.com/2011/05/a-simple-guide-to-database-design-in-mysql/ and http://arachnoid.com/MySQL/ may help). – BenOfTheNorth Nov 21 '15 at 22:29
  • I read the documents and it didn't really help me specifically, because the problem of storing various arbitrarily long arrays for each user is never addressed. If I followed the structure presented in http://arachnoid.com/MySQL/relational_databases.html I should make a table for each user, but I read in many places that it is not recommended (http://stackoverflow.com/questions/7796402/make-a-sql-table-for-every-user-of-website-thousands-with-thousands-of-rows) – mdc Nov 21 '15 at 22:59
  • 1
    Yeah that's definitely not advised. For storing things where you don't actually know what columns you need until you need to store that row you would want something like an Entity Attribute Value table design. It's not always a great design to work with but reading up on that should point you in the right direction. – BenOfTheNorth Nov 21 '15 at 23:06
  • You need to learn what a database *relationship* is, what a foreign key is, and what a LEFT JOIN is. Unless you know what these things are, you cannot be hoping to be doing any SQL programming, and it is fruitless to be asking questions here. – Mike Nakis Nov 21 '15 at 23:24
  • @MikeNakis clearly am not an expert, but I do know what a relationship, a foreign key and a LEFT JOIN is. I had thought of the possibility of making only one table with all data entries with a foreign key to identify the user, but it seemed really messy to me (once there are a lot of entries, finding the entries for one specific user sounds like a pretty slow process). However, if you think that is the best way, I'll certainly trust you on it. – mdc Nov 22 '15 at 02:39
  • 1
    I must apologize for my above comment being somewhat presumptuous. What I was trying to get to is that yes, you will most probably need to break down your data into columns, possibly in different tables, with proper relations, because you have anticipated that your data will expand, and that's the only mechanism which avoids running into trouble when your data expands. – Mike Nakis Nov 22 '15 at 11:13

1 Answers1

1

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

jjc
  • 310
  • 4
  • 8