0

I am creating a MySQL table to store the concept of "a user follows another user". Of course, one user follows another or not, but more than one registry saying the same doesn't make any sense. This is, both fields together are unique:

create table following (
  id_user1
  id_user2
  UNIQUE (id_user1, id_user)
)

I always add a id field as a good practice to every table. However, I have the doubt if an id field makes sense in this table, or the primary key should be id_user1 and id_user2 together.

SOLUTION A:

create table following (
  id PRIMARY KEY
  id_user1
  id_user2
  UNIQUE (id_user1, id_user)
)

SOLUTION B:

create table following (
  id_user1
  id_user2
  PRIMARY KEY (id_user1, id_user)
)

Is there a reason to go for SOLUTION A or SOLUTION B?

user1314836
  • 219
  • 1
  • 4
  • 14
  • 1
    Does this answer your question? [Surrogate vs. natural/business keys](https://stackoverflow.com/questions/63090/surrogate-vs-natural-business-keys) – philipxy Jan 23 '23 at 00:24
  • 2
    `following` is a many-to-many join table representing a relationship between two users. Typically the mapping should look as solution B as there's no much to be gained by adding an extra numeric id to a table. See this question and its answers for more details: https://dba.stackexchange.com/q/15878/117200 – Anthony Accioly Jan 23 '23 at 00:25
  • [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) https://stackoverflow.com/q/932913/3404097 https://stackoverflow.com/q/1207983/3404097 https://stackoverflow.com/q/23918913/3404097 etc etc etc PS What published DB design textbook/reference/presentation are you following? What design method? What are its steps? Where is the 1st place you are stuck? Why/how are you stuck? [ask] [Help] – philipxy Jan 23 '23 at 00:27
  • 1
    If both user ids are not null, and you will never update either user id, solution b – ysth Jan 23 '23 at 03:01
  • Your table implement M:N relation. If this relation does not have additional attributes and if you do not need to rely on it then additional synthetic primary key not needed. Moreover, if the logic does not need in NOT NULL definition for both columns then this table doesn't need in primary key at all. So, depends on your schema logic, you must either select option B or do not alter current table structure. But if your relation acts as separate entity then you'd select option A, w/o alternative. – Akina Jan 23 '23 at 04:36
  • Your explanations have helped me a lot. I have found out that if I want to keep a record of "unfollows" and "re-follows" using a "deleted_at" field I definitely need a `id` primary key in addition to the combination of `id_user1` + `id_user2`. – user1314836 Jan 23 '23 at 17:45
  • @philipxy I didn't even know the term "surrogate". I understand that newbies always ask questions that have already been asked but sometimes we don't even know how to ask our doubts. We focus on our particular problem because we can't imagine that there is a reflection behind it - if we knew, we would also know the answer. In any case, sorry for the inconvenience. – user1314836 Jan 23 '23 at 17:46
  • The "surrogate" titled Q&A is just one I happen to have on hand. Moreover I gave others & "etc etc etc". People don't need to know the right terminology. But one must write many clear, concise & precise phrasings of one's question/problem/goal to (re)search reasonably. Also there's nothing like that in this post. Questions here are duplicates when they have the same answer. You haven't inconvenienced me. PS Your 2nd last comment didn't go to anybody, no `@`. PS Strongly suggest you clearly know what published method/process you are trying to follow for success in both design & asking. – philipxy Jan 24 '23 at 01:23
  • Re (re)search success: Please before considering posting: Pin down code issues via [mre]. Read manuals/references & google error messages & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help]. Reflect research in posts. – philipxy Jan 24 '23 at 01:24

0 Answers0