CODEWITHSUNDEEP
phpmysqlforeign-keysrelationship
Ryckes
Ryckes

Reputation: 34

MySQL one-to-many relationship. Different table, or PHP handling?

I am creating a MySQL database in which I have two tables, one for blogs, and one for their locations. What I have done is a field in blogs which is a string of location ids separated by commas: 1,5,7. Then in my PHP script I can explode() that string and get the locations.

The problem comes if I want to look for all blogs of a location using a MySQL query.

Should I create another table for the relationship? Maybe blog 1 location 1, blog 1 location 5, blog 1 location 7. Three rows to represent the former example. That way I would only select the blogs in that location in the query. The other way I would have to select ALL the blogs and check each of them one by one.

What do you think is faster and cleaner?

Thank you!

Upvotes: 0

Views: 1153

Answers (3)

Josh
Josh

Reputation: 1616

The correct that the best way to do this in a Relational Database setting is the 3rd table, sometimes called a Junction Table, to handle the relationships between Blogs and Locations. What you describe is a many-to-many relationship (or a one-to-many relationship that is going both ways).

Here is a link describing it a little better than me.

Upvotes: 0

Jaydee
Jaydee

Reputation: 4158

A third table to represent the relationships is the only way to go really.

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157863

Different table is cleaner. As a matter of fact, it's the only choice, as it's the very basics of relational databases.

Upvotes: 2

Related Questions