Pizza & Food - database design

Im stuck with Extras functionality - how to design table and fields for the dropdown extras? See my question "Extras functionality (Dropdown / tickboxes)"

If you need to make a dropdown, put the values for the dropdown in a table.
It's OK to put extra values in there, as long as you can easily separate out the values you need in the dropdown.

Exampletable option_labels-------------------id    integer auto_increment PKname  varchar(40)table toppings--------------id               integer  auto_increment PKoption_label_id  integer  foreign key references option_labels(id)item_id          integer  foreign key references items(item_id)price            decimal(10,2)

As soon as you know the item, you can populate the dropdown using:

SELECT ol.name, t.price FROM toppings tINNER JOIN option_labels ol ON (t.option_label_id = ol.id)WHERE t.item_id = '$item_id'

Normalize those tables
This table has a major flaw:

mysql> select * from item_extras;+----------+-----------+-----------+------+| extra_id | option_id | name      | cost |+----------+-----------+-----------+------+|        1 |         1 | Mushroom  | 1.00 ||        2 |         1 | Pepperoni | 1.00 ||        3 |         2 | Mushroom  | 1.00 ||        4 |         2 | Pepperoni | 1.00 ||        5 |         3 | Chips     | 0.50 ||        6 |         4 | Chips     | 0.50 |+----------+-----------+-----------+------+

It's not normalized, put the names in a separate labels table, just like in the example above. If a row (excluding the id) is not unique then your data is not normalized and you're doing it wrong.

Because you are using a lot of joins, it's IMHO best to use InnoDB, it has some cool features to speed up joins that use PK's a lot.

No matter what people say
Don't denormalize until slowness starts.

Use indexes
Put an index on all fields named *_id.
Also put an index on alls fields that are used in where clauses often.
Do not put indexes on fields with low cardinality, so no index on a boolean or enum field!
Indexes slow down inserts and speed up selects.

You have an order table. Then an order item or order line table. With items that can have ingredients on it, you end up with a table that holds the ingredients. With pizza, in particular, you have to worry about halfs, so people can order different things on each half. So far it looks like you are heading in the right direction.

The way I go through design is I figure out the "objects" first. There are tangible objects, like a burger or a pizza, and there are intangible objects, like an order, an order line.

Pizza Hut has one of the best user interfaces for setting up pizza on the web. I would look at it, as it will give you some ideas on how to store the data.