I'm trying to learn about postgres and relationships. So let's take a very common example. Say we have a Users table and a Products table. I know that the classic way to go about it is to phrase the relationship as One User has many Products and One Product has many Users which will result in a Many2Many relationship. But what's the advantage of that over just going One User can have many Products and Many Products can belong to one User, which would give out a One2Many relationship from the User perspective?
Asked
Active
Viewed 37 times
-1
-
It takes three tables to model the User/Product Many-to-Many relationship you describe in your question. If User/Product is One-to-Many in one direction or the other, then you need only two tables. – Mike Organek Jul 31 '23 at 11:42
-
@MikeOrganek so in case of Many-to-Many, where we need 3 tables, there's a User table with user_id & user_name; Product table with product_id & product_name and a 3rd table, let's say Orders, where you have user_id & product_id as columns. So in Orders table, user with id 1 will have 3 entries, if he had ordered 3 products, user with id 2, 4 entries, etc. But in One-to-Many, User table would have user_id, user_name and a products array with product_ids from Products table and Products table would only have product_id, and product_name. So the question is why is one better than the other? – mehayoo Jul 31 '23 at 12:37
-
Do not use array columns. – Mike Organek Jul 31 '23 at 12:54
-
@MikeOrganek Then how would One-To-Many work in this case? User table will have multiple rows with the same user_id and different product_ids from Products table? – mehayoo Jul 31 '23 at 13:02
-
It would not work because it does not work. Use the many-to-many join table. Do not use array columns. – Mike Organek Jul 31 '23 at 13:06
-
In a logical design your description of M:M between products:users is correct. However, (afaik) this cannot be implemented in a physical model by any RDBMS. The solution is introduction of a 3rd table - a resolution table. Let's call it UP (User_Product). Now this table actually contains two 1:M relationships: User:UP and 1:M Product:UP. This **is** your *One2Many relationship from the User perspective* and also a *One2Many relationship from the Product perspective*. – Belayer Jul 31 '23 at 16:58
1 Answers
0
It depends on what you want to achieve, for example, Let me explain relationships in a relational database with a different example. There are three different types of relationships. Now for example in a social media app. One to one, a user can only have one profile. This relationship is between the profile table and the user table.
One to many, a user can write multiple posts, this relationship is known as one to many between the user table and the post table. many to many, now when a user can follow multiple tags, and each tag can be followed by multiple users this represents a many to many relationship. You can also refer to this previous post in StackOverflow for more context.
So if you want your product table to be associated with many users, and for many users to own different products then a manytomany field is more appropriate.
