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.

Does this answer your question? many-to-many relationship in database design — philipxy
– philipxy, Commented Feb 14, 2024 at 21:14

Mark Byers · Accepted Answer · 2012-02-15 19:33:50Z

2

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 ....

edited Feb 15, 2012 at 19:33

answered Feb 15, 2012 at 19:28

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

stdcerr Over a year ago

Huh, I don't really understand that, what kind of fields do I need to create to link the catyegory to the location?

stdcerr Over a year ago

Ah, got it, that's awesome, I just need a table (foreign key) to associate one key to the other, awesome! Thank you!

Collectives™ on Stack Overflow

MySql database design

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related