Re: Database Design Advice

OK Edward, but presumably you are building your menus by select statements, thus you wouldn't have all items from a table intermixed. Don't confuse display with data - everything, whether it be drink, entree, appetizer or dessert is a "menu_item". Fetch menu_items for display (or select lists) according to different criteria. Hope this clarifies it - Miles At 10:24 PM 5/13/2003 -0400, Becoming Digital wrote:

One table, with a field for categories, and if necessary, a flag to identify a particular item as a special. Ordinarily, I would go this route, but the specials are not normal menu items and as such should not otherwise be displayed. Given that the specials are to be updated daily as well, I thought it best to devote a table to them. I'd bet you could even include drinks in the "foods" table. Another aspect I'd considered. However, the drink menu is extensive and there is a section of the site devoted to the restaurant's bar, so again, I thought another table was warranted. Your further comments are welcome. Edward Dudlik Becoming Digital www.becomingdigital.com ----- Original Message ----- From: "Miles Thompson" <milesthompson@ns.sympatico.ca> To: "Becoming Digital" <subs@becomingdigital.com>; "PHP-DB" <php-db@lists.php.net> Sent: Tuesday, 13 May, 2003 14:07 Subject: Re: [PHP-DB] Database Design Advice One table, with a field for categories, and if necessary, a flag to identify a particular item as a special. I'd bet you could even include drinks in the "foods" table. This isn't just a question of database design, but think how much the code is simplified. Cheers - Miles At 01:40 PM 5/13/2003 -0400, Becoming Digital wrote: I realize that this is better suited for the MySQL list but I've been unable to post there for no apparent reason. My emails to the list owner have gone unanswered as well, so I appreciate your tolerance with my posting here for the time being. I'm setting up a database for a new project and I keep wavering on one aspect of the design. The end result is to be a searchable menu for a restaurant's website and I cannot decide if it is best to simply use one table for all menu items (food) or use tables for each category (pasta, appetizers, etc). I currently have a table listing said categories (categories) but I simply can't decide which is better for this application. Thank you all for your advice. Below is some info from MySQL regaring the current design of the DB. SHOW TABLES; +---------------+ | Tables_in_tga | +---------------+

| categories    |
| drinks        |
| food          |
| specials      |

+---------------+ DESCRIBE categories; +-----------+---------------------+------+-----+---------+----------------+

| Field     | Type                | Null | Key | Default | Extra          |

+-----------+---------------------+------+-----+---------+----------------+

| cat_id    | tinyint(3) unsigned |      | PRI | NULL    | auto_increment |
| cat_name  | char(100)           |      | UNI |         |                |

+-----------+---------------------+------+-----+---------+----------------+ DESCRIBE food; +------------+---------------------+------+-----+---------+----------------+

| Field      | Type                | Null | Key | Default | Extra          |

+------------+---------------------+------+-----+---------+----------------+

| item_id    | tinyint(3) unsigned |      | PRI | NULL    | auto_increment |
| cat_id     | tinyint(3) unsigned |      |     | 0       |                |
| item_name  | char(50)            |      |     |         |                |
| item_desc  | char(255)           |      |     |         |                |
| item_price | float(5,2)          |      |     | 0.00    |                |

+------------+---------------------+------+-----+---------+----------------+ Thanks again. Edward Dudlik Becoming Digital www.becomingdigital.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php