How can I associate multiple entries to another entry in another table?

I'm creating a site where people can rent out their house. I currently have two tables in my database.

Properties

&nb开发者_如何学Csp;- property_id

 - address

 - city

Amenities

 - amenity_id

 - amenity_name

The site admin has the ability to add amenities to the database. A landlord logs into the site, where they have the ability to choose which of the amenities their property has(ex: pets, furnished).

I'm trying to figure out a way to associate one or multiple of the amenities to one single property. I'm guessing I need a third table that will somehow keep track which amenities are associated with properties.

How can I associate multiple amenity_id's to one property_id?

---

Create a PropertyAmenity table with simply two columns:

property_id
amenity_id

The primary key should be a composite of both property_id and amenity_id. You might also want to add a user_id and update_date field is auditing is important.

This will allow you to associate a property to many amenities.

foreign key relationship should be between:

Properties.property_id and PropertyAmentity.property_id
Amentities.amenity_id and PropertyAmenity.amenity_id

Example:

Property:
property_id  address             city
100          Property 1 St.      NY
101          Property 2 St.      NY

Amenity:
amenity_id   amenity_name
100          Central Air
200          Swimming Pool

PropertyAmenity
property_id   amenity_id
100           100
100           200
101           100

In the above example, Property 1 has both central air and a swimming pool, while property 2 has just Central Air.

To select the the amenities for a given property you can write sql like:

Select a.* from Property p, PropertyAmenity pa, Amenity a
where p.property_id = pa.property_id
and pa.amenity_id = a.amenity_id
and p.property_id = 100