Implementing association tables

I was wondering if someone could help me organize my MySQL tables in a way that you would consider to be cor开发者_StackOverflowrect (I read somewhere that association tables are what I'm looking for). I'm having trouble implementing them.

Here's an example:

Location            Type                 Event                   Date
Location 1          bar, disco           event1                  friday
Location 1          bar, disco           event2                  saturday
Location 2          bar, restaurant      Event3                  friday

How would you go about this if you wanted to have 'Location 1' in your database only once, and have the events associated with it stored elsewhere? The same applies to the type section. I am having trouble deciding how I should set multiple variables to one location, such as a bar also being a restaurant, etc...

---

The relationship between Locations and Events is an example of a 1-to-many relationship. This means that each individual location can have many events associated with it. These types of relationships are usually implemented by adding a foreign key to the 'many' table (events) that references the primary key of the 'one' table (locations).

The relationship between 'Locations' and 'Types' is an example of a many-to-many relationship. This means that a location can have many types and a type can be related to many locations. These types of relationships are usually implemented with a link table, which contains foreign keys for the related rows. The link table usually has a composite primary key of the two foreign keys, which means that one location can't be linked to the 'bar' type twice.

So, the following table structures might be suitable for you:

Location: ID (primary key), LocationName, ...
Events: ID (primary key), LocationID (foreign key), Date, Name, ...
LocationTypes: LocationID (fk), TypeID (fk)
Types: ID (pk), Name, ...

---

To query the information across several of the tables, you have to use joins. For the 1-to-many relationship, the following query will work:

SELECT
    l.LocationName, e.Name, e.Date
FROM Location l
    JOIN Events e ON l.ID = e.LocationID

For a many-to-many relationship, the following query will join together the information.

SELECT
    l.LocationName, t.Name as TypeName
FROM Location l
    JOIN LocationTypes lt ON l.ID = lt.LocationID
    JOIN Types t ON lt.TypeID = t.ID

These examples just show a standard inner join, there are other join types that may better suit your needs.

---

Supposing that type is the type of the location

tblType
id int
name varchar

tblLocation
id int
name varchar

tblLocationType (m-n relation)
fk_type int (ref. tblType.id)
fk_location int (ref. tblLocation.id)

tblEvent
id int
name varchar
place int (ref. tblLocation.id)
date DATETIME

EDIT: Your exapmle would read:

tblType
id name
1  bar
2  disco
4  restaurant

tblLocation
id name
1  Location 1
2  Location 2

tblLocationType
1 1
2 1
1 2
3 2

tblEvent
1 event1 1 whenever
2 event2 1 whenever
3 event3 2 whenever