Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Database Design Advice

0 views
Skip to first unread message

Becoming Digital

unread,
May 13, 2003, 1:40:40 PM5/13/03
to PHP-DB
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


Mike Karthauser

unread,
May 13, 2003, 2:03:14 PM5/13/03
to Becoming Digital, php...@lists.php.net
on 13/5/03 6:40 pm, Becoming Digital at su...@becomingdigital.com wrote:

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

http://www.databasejournal.com/sqletc/article.php/26861_1474411_1

It should be very obvious what structure you need once reading this..
--
Mike Karthauser
Managing Director - Brightstorm Ltd

Email >> mi...@brightstorm.co.uk
Web >> http://www.brightstorm.co.uk
Tel >> 0117 9426653 (office)
07939 252144 (mobile)

Snailmail >> Unit 8, 14 King Square,
Bristol BS2 8JJ

Miles Thompson

unread,
May 13, 2003, 2:07:06 PM5/13/03
to Becoming Digital, PHP-DB

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

>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php

Becoming Digital

unread,
May 13, 2003, 2:52:37 PM5/13/03
to php...@lists.php.net
Thanks, folks. I appreciate the pointers. This just goes to show that
extensive knowledge of relational theory can have little effect on real-life
projects.

Edward Dudlik
Becoming Digital
www.becomingdigital.com

Becoming Digital

unread,
May 13, 2003, 10:24:03 PM5/13/03
to PHP-DB, Miles Thompson
> 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" <milest...@ns.sympatico.ca>
To: "Becoming Digital" <su...@becomingdigital.com>; "PHP-DB"
<php...@lists.php.net>
Sent: Tuesday, 13 May, 2003 14:07
Subject: Re: [PHP-DB] Database Design Advice

Miles Thompson

unread,
May 14, 2003, 8:58:42 AM5/14/03
to Becoming Digital, PHP-DB
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

Becoming Digital

unread,
May 14, 2003, 11:54:21 AM5/14/03
to PHP-DB
> OK Edward, but presumably you are building your menus by select statements,
> thus you wouldn't have all items from a table intermixed.

Agreed, but the issue here is the quantity of specials. This particular client
has nearly a dozen specials updated daily, all of which then need to be broken
down into lunch, dinner, and dessert subcategories.

Thinking about it further, I could add a category "special" to my categories
table and include a date column in the "food" table. This resolves everything
but the sub-specials issue, but all that's required there is a bit of further
manipulation to the setup.

Thanks, I think I've learned something. :)

0 new messages