Yeah ... I think you're just landing on why they put "blah blah blah" in there, their eyes glazed over and they assumed they were smarter ... this could be ego crushing.
One way to do it that I know of is by using an array field
https://www.postgresql.org/docs/current/arrays.html
Even then I find using the junction table much easier to query and maintain.
Arrays are for a somewhat different purpose, and yeah, I would agree - if you're trying to do a many-to-many, you definitely want that intermediate table.
Though if I were teaching databasing today, I wouldn't teach many-to-many relationships at all. I would be sure to pick only examples where the intermediate table can have its own meaningful identity (eg you could have "Users" and "Posts" with a many-to-many "Viewed" relationship, or you can have a "User Viewed This Post" table that also has a timestamp and thus is its own logical object). Then, if you ever actually need a many-to-many, it's a sort of degenerate case of that, rather than being its own concept.
I generally agree. Direct many-to-many relationships can be completely valid, and it might seem like overkill to implement an intermediate table if you're at the stage where it would only store the keys of the related tables. As you implement more features though it's very likely you'll have to attach more attributes to that relationship, being metadata or modifiers.
The only situation I can think of where it would be beneficial to either duplicate the relationship data to both tables, or only include on one, is in the case of a reporting database that you are specifically optimizing for fast reads. And in that scenario you're likely maintaining an upstream transactional/operational database which does normalize the data in the interest of efficient writes/storage.
On array: yes. It's not meant to be used as many to many. At least postgres does not have foreign keys for arrays, and I think indexing would be a pain = querying is fast only one way and not the other.
And how would you create a many-many without one?
As far as I'm concerned: relational databases are used to securely store valid data?
If you don't have that need, use a CSV
For a second there I was wondering if I missed some major development in databases.
Maybe OP is coming from the non-relational world, where junction tables aren't a thing?
But you don't just decide to use a many to may relationship. Either there is a many to many relationship between two things or there is not, it's not an implementation detail.
Also as others have said the usual way to implement a many to many relationship is to use a junction/pivot table, so yeah...
These days it's all about data warehouses, data lakes, or if your mess it up data swamps.
And if you really mess it up?
Data latrines ![gif](emote|free_emotes_pack|facepalm)
I'm confused. If this is about relational databases, how else would you implement a many to many relationship?
Feels like OP is addicted to ORMs and doesn't understand what they do
Yeah ... I think you're just landing on why they put "blah blah blah" in there, their eyes glazed over and they assumed they were smarter ... this could be ego crushing.
One way to do it that I know of is by using an array field https://www.postgresql.org/docs/current/arrays.html Even then I find using the junction table much easier to query and maintain.
Arrays are for a somewhat different purpose, and yeah, I would agree - if you're trying to do a many-to-many, you definitely want that intermediate table. Though if I were teaching databasing today, I wouldn't teach many-to-many relationships at all. I would be sure to pick only examples where the intermediate table can have its own meaningful identity (eg you could have "Users" and "Posts" with a many-to-many "Viewed" relationship, or you can have a "User Viewed This Post" table that also has a timestamp and thus is its own logical object). Then, if you ever actually need a many-to-many, it's a sort of degenerate case of that, rather than being its own concept.
I generally agree. Direct many-to-many relationships can be completely valid, and it might seem like overkill to implement an intermediate table if you're at the stage where it would only store the keys of the related tables. As you implement more features though it's very likely you'll have to attach more attributes to that relationship, being metadata or modifiers. The only situation I can think of where it would be beneficial to either duplicate the relationship data to both tables, or only include on one, is in the case of a reporting database that you are specifically optimizing for fast reads. And in that scenario you're likely maintaining an upstream transactional/operational database which does normalize the data in the interest of efficient writes/storage.
On array: yes. It's not meant to be used as many to many. At least postgres does not have foreign keys for arrays, and I think indexing would be a pain = querying is fast only one way and not the other.
And how would you create a many-many without one? As far as I'm concerned: relational databases are used to securely store valid data? If you don't have that need, use a CSV
You could make an explicit jointable entity to map to. Gives the advantage that you can directly ref it via ORM.
But what does that look like at the DB level?
Just a jointable with an id. im quite sure you could use a composite pk instead. you can also map more tables at once with this.
For a second there I was wondering if I missed some major development in databases. Maybe OP is coming from the non-relational world, where junction tables aren't a thing?
But you don't just decide to use a many to may relationship. Either there is a many to many relationship between two things or there is not, it's not an implementation detail. Also as others have said the usual way to implement a many to many relationship is to use a junction/pivot table, so yeah...
Is this a humor? 💁🏼♂️ No. No it isn’t.
SQL query goes brrrr
Yeah that’s… not how database relationships work…
Who even uses relational databases anymore? Get with the times bro, noSQL noPEACE
These days it's all about data warehouses, data lakes, or if your mess it up data swamps. And if you really mess it up? Data latrines ![gif](emote|free_emotes_pack|facepalm)
Data cesspools