MySql database design

Question

Table locations contains locID, lat & long & name values of a certain location.

Table categories contains ID, category-name & locID.

How do I make categories assignable to multiple locations?

It doesn't make sense to have the same category stored multiple times, once for every locID.

Answer 1

You can use a join table to model the many-to-many relationship.

location_category
location_id   category_id
1             1
1             2
2             1
2             3
3             4

Add foreign key constraints to prevent invalid values from being entered into the table:

• From location_category.location_id to locations.locID
• From location_category.category_id to categories.ID

Also make (location_id, category_id) the primary key for the table to prevent adding a category to the same location multiple times.

---

When reading the data from the table, use JOINs to get the related data from the main tables:

SELECT ...
FROM location_category
JOIN locations ON location_category.location_id = locations.locID
JOIN categories ON location_category.category_id = categories.ID
WHERE ....