0

I am struggling to create a table with a structure that shows values of user-date relation. Sample pseudo-database:

+------+----------+----------+----------+-----
| user | 01012016 | 02012016 | 03012016 | ...
+------+----------+----------+----------+-----
| 1    | 2        | 57       | ...      | ...
| 2    | 12       | 73793    | ...      | ...
| 3    | 56       | 468      | ...      | ...
| 4    | 1689     | 12357    | ...      | ...
| 5    | 946      | 13       | ...      | ...
+------+----------+----------+----------+-----

Values are integer.

According to MySQL Reference Manual, key range is limited.

What structure a table should have to allow unlimited amount of columns and rows in this case?

I am aware that I can create a separate table for each user, but the idea is to keep everything optimized - in a smallest possible number of tables.

I am also aware of a simple structure (user, date, value), but there will be a lot duplicates, to be specific, in 'user' and 'date' columns:

+------+----------+----------+
| user | date     | value    |
+------+----------+----------+
| 1    | 01012016 | 57       |
| 1    | 02012016 | 73793    |
| 1    | 03012016 | 468      |
| 2    | 01012016 | 12357    |
| 2    | 02012016 | 13       |
| 2    | 03012016 | 618      |
| ...  | ...      | ...      |
+------+----------+----------+

And even if I decide to use this structure, what key should I define a primary one?

2

Your second method is the right way to represent the data in the database.

If you want a user/date pair to appear only once, then create a unique index/constraint to enforce this:

create unique index unq_simplestructure_user_date on simplestructure(user, date);

Tables in SQL have a pre-defined structure. Database systems (for better or worse) are not designed to hold arbitrary numbers of columns. In cases where this is a requirement (and your problem is not one of these cases), then a JSON representation (or something similar) can be used.

| improve this answer | |
  • all right, but how to deal with duplicates? – Jessica Dec 4 '16 at 17:13
  • 3
    I don't understand the question. What is the issue with duplicates? This totally supports multiple rows with the same value in user or the same value in date. Combinations are disallowed by the unique index/constraint, but just don't include that if you want to allow duplicated pairs. – Gordon Linoff Dec 4 '16 at 17:29
  • imo, it will take more storage to store duplicates. also it will take more time to look in bigger database – Jessica Dec 4 '16 at 19:07
  • @user2791506 They are not duplicates. The many-to-many relationship is a standard feature of many databases. Yes, 2NF does use more disk space than creating many columns, but having many columns creates problems on even modestly sized tables, including having to resize the entire file every time you need to add a new date, plus complications trying to get the data back out. You will not get your desired performance by using many columns. – phyrfox Dec 5 '16 at 6:38
  • Make that the PRIMARY KEY, not just UNIQUE. (The PK is, but design, UNIQUE.) – Rick James Dec 5 '16 at 18:58
2

You should take a look at Second Normal Form (2NF), one of the recommended forms designed for optimized database data.

In 2NF, the users should be in one table, and references to the users should be in other tables, as appropriate. Since you're storing data for various dates with values, the table should definitely have the form of user, date, and value.

If the date is significant and has other data not related to that value, you might even end up with three tables, not just two, in order to satisfy 2NF, and this is perfectly normal.

If you're still not convinced, imagine you're now up to 100 days, and you need to figure out the total of the values. Using your scheme, you end up with a query that looks like this:

select field1+field2+field3+field4...field100
from mytable where user = 5

Conversely, to find the total for 100 days using 2NF, you simply do this:

select sum(value) from mytable where user = 5;

This is easier to maintain in the long run, even if the database allowed for unlimited columns, which it doesn't.

As for the Primary Key (PK), it's obvious: you should use a composite PK, consisting of the user's ID and the date value:

create table mytable(
    `user` int,
    `date` date,
    `value` int,
    primary key(`user`,`date`),
    index(`date`)
)

Your PK should ideally use the order you intend to use more frequently, and then index the other field if you're interested in doing faster queries involving the date field.

| improve this answer | |
0

If you are asking about preventing duplicate rows, 'constraining' (user, date) to be "unique" (via UNIQUE KEY or PRIMARY KEY) is sufficient. (See two other answers.)

If, on the other hand, you need to add to the value, then use INSERT ... ON DUPLICATE KEY UPDATE ..., which either INSERTs (if no uniqueness constraint is violated) or UPDATEs.

INSERT INTO tbl
    (`user`, `date`, `value`)
    VALUES
    (12, '2016-01-03', 33)
ON DUPLICATE KEY UPDATE
    `value` = `value` + VALUES(`value`).

An aside: Please don't name columns date and value; those conflict with semi-reserved words DATE (a datatype) and VALUE (used in INSERT syntax).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.