1

For example, I have a table of Users and a table of Blogs and a separate table called User_To_Blogs which contain only User and Blog IDs.

What are the benefits of such a table as opposed to having a User ID column in the Blog table?

Johnathan Au
  • 5,244
  • 18
  • 70
  • 128
  • 1
    what if a blog is owned by multiple users? now you need `user1`, `user2`, `user3`, .... `user99999999` in the blog table. – Marc B May 14 '15 at 16:27
  • 1
    you should google about `normalization` to understand that, you can start with http://stackoverflow.com/a/1258776/4421474 or any othe link you can find – Alex May 14 '15 at 16:29
  • Thank you. Both of your comments were helpful. It's been a while since I last dealt with MySQL! – Johnathan Au May 14 '15 at 16:32
  • possible duplicate of [Normalization in MYSQL](http://stackoverflow.com/questions/1258743/normalization-in-mysql) – Johnathan Au Jul 02 '15 at 14:19

2 Answers2

3

These sorts of tables are useful for many-to-many relationships, where a user can have many blogs, and a blog can have many authors, for example.

Perhaps a better example is the person -> employer relationships, where a person can have worked for many employers, and an employer can have many employees

Rowland Shaw
  • 37,700
  • 14
  • 97
  • 166
  • It is also worth noting that such tables need not be composed exclusively foreign keys. In the blog-related example, the table could also have the user's relationship to the blog (owner, editor, allowed to view, etc...) – Uueerdo May 14 '15 at 16:39
0

Let's say your blog table has information like blog name, blog start date, blog URL, etc. If you have the user ID in that table, every row will have the same blog information repeated. If you define the blog record in its own table, you define it once and reference it as many times as you want in the user-blog table.

Mark Leiber
  • 3,118
  • 2
  • 13
  • 22