Is there a need for an unsigned integer id for one to one relations?

There is a table of users (users), there is a table with fields-counters (user_counts)

normal users like everyone
user_counts with fields: id, user_id, comments, likes, votes, notifications

UPD: user_id-unique, there could be 2 records with the same user_id user_counts

I wonder how necessary an unsigned integer id for the table user_counts if everything is still fetching/updates occur on user_id? Maybe it helps some of the internal operations of the database engine be faster or can be removed?
June 8th 19 at 16:33
2 answers
June 8th 19 at 16:35
Solution
in this particular case user_counts stores aggregated values comments, likes, votes, notifications and there is clearly no need a surrogate key, because the user_id uniquely identificeret record.

p.s. read what is a surrogate key, why you need it.
June 8th 19 at 16:37
It is common practice when designing tables. Is primary key is foreign key. In this case, the user_id in the table. The primary key must always be unique. If not ID, then what do you take as the uniqueness of all rows? Otherwise we'll have to take comments or votes or Likes or notifications, or a combination of them. But where is the guarantee that you will not get 2 the same combination of for example from to comment notifications.? Yes, and put the key with the same comment xxx characters? ))
I hope will explain clearly )
So user_id in user_counts unique. One to One, there could be 2 records with two identical user_counts user_id - Tod.Erdma commented on June 8th 19 at 16:40
in user_counts user_id is a foreign key which should be on the idea is associated in sql logic.
take for example a more normal implementation:
- forget the plural, each comment is one line with its own unique ID, which is tied to user_id
for example, in users - user_id = 1
Table comments:
id, user_id, comment
id, user_id, comment
id, user_id, comment
translate this into values
1, 1, privet
2, 1, poka
3, 1, kak dela
select comments.comment from comments where users_user_id = comments comments.user_id
as you can see user_id in comments is not unique, it's the binding post with a table of users. - thalia.Hermann50 commented on June 8th 19 at 16:43
> in user_counts unique user_id in the comments of course will be different

user_counts stores just cached number of different actions of the user - Tod.Erdma commented on June 8th 19 at 16:46
I pokazyvaet that design in your case all through one place)) read about database normalization?
I certainly do not know what are your knowledge, so it's hard just to answer. Could there be some specific situation or problem, and you just can learn)) - thalia.Hermann50 commented on June 8th 19 at 16:49
did not understand what the problem is. What, for example, suggests? - Tod.Erdma commented on June 8th 19 at 16:52
popular experience when the cache amount, for example, like this:

Users table
id | name | ... | posts_counts | comments_count | ...

And when you create new review create a review and incremental users.comments_count. I purely for convenience I decided to separate the number into a separate table and separate recording key user_id. That is, the increment is now happening here

increment user_counts.comments where user_id = {userId}. Don't see a problem. I would be glad if you tell me what is wrong in this approach :) - Tod.Erdma commented on June 8th 19 at 16:55
now it just will not delve into the complex logic of chelovekov, only the bar, but... wait, why do you count comments? How was it helpful? ) mean in terms of logic, not except to show how many people in General made comments to the example for the twentieth time(though it is...) :D - thalia.Hermann50 commented on June 8th 19 at 16:58
in the sense of how useful? Displayed in the interface next to each profile, people are interested to watch, appreciate.

For example, in the toaster in the profile also displays the number of (Questions, answers). I doubt that every time they recalculate

Did not understand where the problem is in the architecture here. - Tod.Erdma commented on June 8th 19 at 17:01
I then do not understand XA
I don't understand the table. Then everything is OK))
Still profile look in fact not often, but every comment, every like, every action will overload the system with additional insert to the table counters. But by itself, the count function is not integrated, so there I'd be thinking that you are rational that is)) - thalia.Hermann50 commented on June 8th 19 at 17:04

Find more questions by tags MySQLDatabases