CODEWITHSUNDEEP
mysqlsqldatabase
user3413723
user3413723

Reputation: 12213

Mysql Optimization - Each User stores a lot of data

I am making a web application using php and mysql that allows a user to enter data over time. When I think of how to structure the data, it would make sense to have a separate table for each user structured like this: (see below, not including (user) column). I know that making a separate table for each user is not a good idea from other posts, so I'm wondering how to structure the data. My other thought was to have another column for each user. When I want to retrieve the past 10 days entries I could just do SELECT Value1, Value2 WHERE DATE="1/1/14", USER="dave". That way would seem to work, but if my application has, say, 10000 users, each logging, say 10 values at a time, I could get a huge table. That may not be a problem, as I guess mySQL may be optimized for that, but I just want to get another opinion on the best way.

Date    Value1  Value2  Value3       (user)
1/1/14  1       1       2            (dave)
1/2/14  2       2       2            (dave)
1/2/14  2       2       1            (tom)
1/3/14  3       2       2            (dave)

Upvotes: 0

Views: 67

Answers (2)

Devon Bessemer
Devon Bessemer

Reputation: 35337

As Gordon said above: 100,000 isn't a huge table. Even a million rows is easily handled by a properly indexed table.

How I would handle this:

If each user has static fields (every user has the same fields), create a table like so:

USER_ID
FIELD1
FIELD2
FIELD3
FIELD4

Where the primary key is the USER_ID.

If each user has dynamic fields:

USER_ID
FIELD_NAME
VALUE

Where the primary key is a composite key of USER_ID,FIELD_NAME. This way you could store the value of any field you desire for that user and you could have multiple rows/fields for each user.

Upvotes: 1

Joshua Huber
Joshua Huber

Reputation: 3533

Keep it in one table. Don't do the table-per-user way. Also, don't use the reserved word DATE or USER for a column name, instead column names like ENTRY_DATE and USERNAME.

EDIT: Here's what I'm thinking, a normalized schema that allows dynamic fields. Play with it at http://sqlfiddle.com/#!2/54b5b/3/0

CREATE TABLE users (
    id      INTEGER UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY
  , name    VARCHAR(100)
  , email   VARCHAR(100)
  -- and whatever other user info you want to keep
);

CREATE TABLE measures (
    id    INTEGER UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY
  , name  VARCHAR(100)
);

CREATE TABLE measure_facts (
    id         INTEGER UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY
  , entry_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
  , user_id    INTEGER UNSIGNED NOT NULL REFERENCES users(id)
  , measure_id INTEGER UNSIGNED NOT NULL REFERENCES measures(id) 
  , value      INTEGER 
  , KEY (user_id, entry_date, measure_id)
);

INSERT INTO users (name) VALUES ('dave'), ('tom');

INSERT INTO measures (name) VALUES ('measure1'), ('measure2'), ('measure3');

INSERT INTO measure_facts (user_id, entry_date, measure_id, value) VALUES
    (1, '2014-01-01', 1, 1)
  , (1, '2014-01-01', 2, 1)
  , (1, '2014-01-01', 3, 2)

  , (1, '2014-01-02', 1, 2)
  , (1, '2014-01-02', 2, 2)
  , (1, '2014-01-02', 3, 2)

  , (2, '2014-01-02', 1, 2)
  , (2, '2014-01-02', 2, 2)
  , (2, '2014-01-02', 3, 1)

  , (1, '2014-01-03', 1, 3)
  , (1, '2014-01-03', 2, 2)
  , (1, '2014-01-03', 3, 1);

and then a query:

SELECT entry_date, m.name measure_name, value, u.name user_name
  FROM measure_facts
  JOIN measures m ON (measure_id = m.id) 
  JOIN users    u ON (   user_id = u.id);

Upvotes: 1

Related Questions