Mysql Relational Database Categories

Question

I have two tables I'm trying to make a input where users can search for a category for example pizza the database will find all restaurants within that category. My initial thoughts are to make the id of the category field in the table categories the primary key matching it to the restaurant table category field. However the problem I ran into is that only limits a restaurant to one. What would be the best configuration for what I'm asking.

Restaurants Table

+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| idRestaurant | int(11)     | NO   | PRI | NULL    |       |
| Name         | varchar(45) | YES  |     | NULL    |       |
| Password     | varchar(45) | YES  |     | NULL    |       |
| Email        | varchar(45) | YES  |     | NULL    |       |
| Number       | varchar(45) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

Categories Table

+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| idCategories | int(11)     | NO   | PRI | NULL    |       |
| Category     | varchar(45) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

Better u create another table.. that contain detail about what are all the catagories in currently having in that restaurant — Hrithu
– Hrithu, Commented May 30, 2014 at 5:44

M Khalid Junaid · Accepted Answer · 2014-05-30 05:42:40Z

1

You are looking for a junction table as restaurant_categories which will maintain the relation for a restaurant to have more than one category

restaurant_categories

+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     | NO   | PRI | NULL    |       |
| idCategories | int(11)     | NO   |     | NULL    |       |
| idRestaurant | int(11)     | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

This way you can assign multiple categories to a restaurant and for search you can use simple joins among your tables

select distinct r.* from Restaurants  r
join restaurant_categories rc on(r.idRestaurant = rc.idRestaurant )
join Categories c on (c.idCategories =rc.idCategories )
where c.Category ='pizza'

answered May 30, 2014 at 5:42

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

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

6 Comments

user2872510 Over a year ago

why do you have an extra id field

M Khalid Junaid Over a year ago

@user2872510 it denotes strong relations ship but if you don't need then just remove it ,its only for example

user2872510 Over a year ago

so are the idCategory and idRestaurant the primary keys and the foreign keys?

M Khalid Junaid Over a year ago

In restaurant_categories(idRestaurant ,idCategories) are foreign keys and idRestaurant ,idCategories in their own table behave as a primary key

M Khalid Junaid Over a year ago

@user2872510 this is a new question you should ask new one and if you want me to answer that post back the link here,also please post full details regarding your problem and the query or sample code you tried

|

Mark Miller · Accepted Answer · 2014-05-30 05:42:15Z

1

You need another table to store the relationships between Restaurants and Categories. This new table should have two fields:

Restuarant_id, category_id

This way you can store a row for every category belonging to a restaurant.

answered May 30, 2014 at 5:42

Mark Miller

7,4472 gold badges19 silver badges23 bronze badges

Comments

Amadan · Accepted Answer · 2014-05-30 05:42:33Z

Having a foreign ID key of one table (restaurants.category_id) corresponding to a primary field of another (categories.id) is the usual way of implementing a 1:N relationship. In this case, you would say that "restaurant belongs to a category" or that "category has many restaurants" (you can hear these terms very often from Rails developers).

If you wish to be able to assign multiple categories to restaurants, and multiple restaurants to a category, it is a N:N relationship ("has and belongs to many"), for which you need a third table, commonly known as a join table or a relationship table. In the original tables you'd just have their respective primary IDs; the new table would have two foreign keys to pair up restaurants with categories (restaurant_categories.restaurant_id and restaurant_categories.category_id).

Tibs · Accepted Answer · 2014-05-30 05:52:18Z

1

How to define relationships between tables in an Access database

Read this. Hope this will help.

answered May 30, 2014 at 5:52

Tibs

7756 silver badges16 bronze badges

Comments

Steven · Accepted Answer · 2014-05-30 05:56:17Z

1

Why don't you just add a field categoryId in your table 'Restaurants' ?

You will be able to do requests like that :

SELECT idRestaurant FROM RestaurantTable WHERE categoryId = 1

With this structure, if you want to make a research by category you can do that :

SELECT r.Name FROM categories as c 
LEFT JOIN restaurants as r ON r.categoryId = c.idCategories 
WHERE c.Category LIKE '%Pizza%'

answered May 30, 2014 at 5:56

Steven

3333 silver badges10 bronze badges

Comments

fortune · Accepted Answer · 2014-05-30 06:01:19Z

1

Add a third table say RestaurantsCategories

+--------------+-------------+------+------------------------                               
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+------------------------                              
| idRestCat    | int(11)     | NO   | PRI | NULL    |       |
| idRestaurant | int(11)     | NO   |     |         |       |
| idCategories | int(11)     | NO   |     |         |       |      
+--------------+-------------+------+-----+---------+-------+

answered May 30, 2014 at 6:01

fortune

3,3721 gold badge23 silver badges30 bronze badges

Collectives™ on Stack Overflow

Mysql Relational Database Categories

6 Answers 6

6 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

6 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related