Creating optimized table without duplicates in MySQL 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?
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.