Best way to define a MySQL relationship

Question

I'd like to create something like this:

A users table, a address table, and a posts table.

A user can have a single address, but can have multiple posts. This would require a mysql many-to-many relationship, correct? And how should i tie them all together? An example would be great. Thanks!

Answer 1

users table
-----------
id      PK
name


posts table
-----------
id       PK
user_id  FK
title


address table
-------------
id        PK
user_id   FK
city

PK = Primary Key
FK = Foreign Key

To select all posts of a specific user you can do

select p.*
from posts p
inner join users u on u.id = p.user_id
where u.name = 'tom'