I am having trouble figuring out the best way to do this. My site has multiple restaurants. Each restaurant can have [b]multiple menus[b], each menu has multiple categories, and each category has multiple items.
There are similar menus between restaurants (breakfast, lunch, dinner, etc), and similar categories between menus (appetizers, entrees, desserts, drinks, etc), but items are unique to a specific restaurant, menu, and category.
Here is the schema I imagine. Is there a better way? (Colors are foreign key/primary key pairs)
the restaurant_menus table tells you which menus each restaurant can have, while the restaurants_menus_categories table tells you which categories are available in each menu in each restaurant
if you didn’t have those tables, and had only the restaurants_menus_categories_items table, then there would be no way to control which items were available in each category of a menu in a particular restaurant
the foreign keys to these tables implement an important concept called relational integrity
with the restaurants_menus and restaurants_menus_categories tables in place, and the foreign keys properly defined, you can now be assured that categories can only be added to menus that the restaurant offers, and items can only be added to categories that are available in the menus
without those tables, it would be possible to add ice cream to the entrees category in the breakfast menu
BTW if anyone wants to see the various queries used to get things out of this DB schema, they are all in this class here:
<?php
class MenuFactory {
private $db, $restaurant_id;
/* constructor
* $db is a mysqli link, $restaurant_id is an int. */
public function __construct($db, $restaurant_id) {
$this->db = $db;
$this->restaurant_id = $restaurant_id;
}
/* getAllMenus()
* returns numerical array with an index for each menu.
* each index contains an array of the basic menu information */
public function getAllMenus() {
$q = "SELECT menus.id, menus.name
FROM menus
LEFT JOIN restaurants_menus AS r_m ON r_m.restaurant_id = '" . $this->restaurant_id . "'
AND r_m.menu_id = menus.id";
$menus = array();
if($result = $this->db->query($q)) {
while($menu = $result->fetch_object()) {
$menus[] = array('id' => $menu->id, 'name' => $menu->name);
}
} else { die($this->db->error); }
return $menus;
}
/* getMenuCategories($menu_id)
* returns a numerical array of menu categories for the given menu.
* each index contains an array of basic category information */
public function getMenuCategories($menu_id) {
$q = "SELECT c.id, c.name, r_m_c.position
FROM menu_categories AS c
INNER JOIN restaurants_menus_categories AS r_m_c ON r_m_c.category_id = c.id
AND r_m_c.restaurant_id='" . $this->restaurant_id . "'
AND r_m_c.menu_id='$menu_id'";
$categories = array();
if($result = $this->db->query($q)) {
while($cat = $result->fetch_object()) {
$categories[] = array('id' => $cat->id, 'name' => $cat->name);
}
} else { die($this->db->error); }
return $categories;
}
/* getCategoryItems($menu_id, $cat_id)
* returns a numerical array of menu items for the given menu and category.
* each index contains an array of basic menu item information */
public function getCategoryItems($menu_id, $cat_id) {
$q = "SELECT id, name, description, image, price
FROM restaurants_menus_categories_items
WHERE restaurant_id='" . $this->restaurant_id . "'
AND menu_id='$menu_id'
AND category_id='$cat_id'";
$items = array();
if($result = $this->db->query($q)) {
while($item = $result->fetch_object()) {
$items[] = array('id' => $item->id,
'name' => $item->name,
'description' => $item->description,
'image' => $item->image,
'price' => $item->price
);
}
} else { die($this->db->error); }
return $items;
}
/* getFullMenu($menu_id)
* returns array containing ALL categories and ALL items associated with given menu
* return array contains indices: [id, name, categories = [id, name, items = [item info]]]
*/
public function getFullMenu($menu_id) {
$menu = array();
$menu['id'] = $menu_id;
// basic menu info
$q = "SELECT name FROM menus WHERE id='$menu_id' LIMIT 1";
$result = $this->db->query($q) or die($this->db->error);
$row = $result->fetch_object();
$menu['name'] = $row->name;
// categories
$menu['categories'] = $this->getMenuCategories($menu_id);
// items
for($i = 0; $i < count($menu['categories']); $i++) {
$menu['categories'][$i]['items'] =
$this->getCategoryItems($menu_id, $menu['categories'][$i]['id']);
}
return $menu;
}
/* getAllFullMenus()
* returns array containing ALL menus and ALL categories and ALL items associated with restaurant
* return array numerical array with each index pointing to a return value of $this->getFullMenu($menu_id)
*/
public function getAllFullMenus() {
$basicMenus = $this->getAllMenus();
$menus = array();
for($i = 0; $i < count($basicMenus); $i++) {
$menus[$i] = $this->getFullMenu($basicMenus[$i]['id']);
}
return $menus;
}
}
?>
