Menu
Let's say I have a simple database with tables 'posts' and 'tags'. Posts can have many tags and tags can belong to many posts.
What is the best way to structure the database? I thought of using a list/serialize:
tags
idx tag_id, str tag_name
posts
idx post_id, str title, list tag_ids
OR having another table with the associations. Problem is using this I don't even know how to structure the query to pull the associated tag names when I get a post.
posts
idx post_id, str title
post_tags
fk post_id, fk tag_id
I actually I don't like either of them. Is there a better way?
553
When you have a many-to-many relationship between dimension-type tables, we provide the following guidance: Add each many-to-many related entity as a model table, ensuring it has a unique identifier (ID) column. Add a bridging table to store associated entities. Create one-to-many relationships between the three tables.
Connect the three tables to create the many-to-many relationship. To complete the many-to-many relationship, create a one-to-many relationship between the primary key field in each table and the matching field in the intermediate table. For details on how to do this, see Get started with table relationships.
To define a one-to-many relationship between two tables, the child table has to reference a row on the parent table. The steps required to define it are: Add a column to the child table that will store the value of the primary identifier.
Relational databases don't support direct many-to-many relationships between two tables. Then, how to implement many-to-many relationships in SQL? To create a many-to-many relationship in a database, you'll need to create a third table to connect the other two.
The post_tags is the proper means of implementing a many to many relationship in the database.
The only addition I'd make to what you posted is that both columns in it should be the primary key to ensure there are no duplicates.
184
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
