Mysql – Tick boxes and Dropdown

Have a look at the image below:

This is "Plain Buger" item – customer can select sauces and extras with it. There are different sizes for Plain Burger.

I need to translate this UI into database design, here what I have came up with:

mysql> select * from category;
+----+---------+
| id | name    |
+----+---------+
|  1 | Burgers |
|  2 | Pizzas  |
+----+---------+

–

mysql> select * from items;
+----+-------------+--------------+
| id | category_id | name         |
+----+-------------+--------------+
|  1 |           1 | Plain Burger |
+----+-------------+--------------+

Plain Burger is belong with "Burgers" category (category_id = 1)

–

mysql> select * from items_options;
+----+---------+--------+-------+
| id | item_id | name   | price |
+----+---------+--------+-------+
|  1 |       1 | Small  |  3.50 |
|  2 |       1 | Medium |  4.50 |
|  3 |       1 | Large  |  5.50 |
+----+---------+--------+-------+

Plain Burger come with different sizes and the price.

–

mysql> select * from extras;
+----+-------------+-------+
| id | name        | price |
+----+-------------+-------+
|  1 | ketchup     |  0.00 |
|  2 | Mayo        |  0.00 |
|  3 | Boiled Rice |  0.00 |
|  4 | Chips       |  0.00 |
+----+-------------+-------+

–

mysql> select * from groups;
+----+--------+--------------------+-----------+
| id | name   | subject            | type      |
+----+--------+--------------------+-----------+
|  1 | Sauces |                    | tickboxes |
|  2 | Extras | Choose your extras | dropdown  |
+----+--------+--------------------+-----------+

There is where you can create group of extras/sauces/sides/topping for pizzas.
As you can see on the UI, Plain Burger come with Sauces and Extras

–

mysql> select * from groups_extras;
+----+----------+----------+
| id | group_id | extra_id |
+----+----------+----------+
|  1 |        1 |        1 |
|  2 |        1 |        2 |
|  3 |        2 |        3 |
|  4 |        2 |        4 |
+----+----------+----------+

After creating the groups, I need to associate the group with the extras.
Example: Sauces (group_id = 1) are related with Ketchup and Mayo

–

mysql> select * from groups_extras_options;
+----+------------------+-----------+
| id | items_options_id | groups_id |
+----+------------------+-----------+
|  1 |                1 |         1 |
|  2 |                2 |         1 |
|  3 |                3 |         1 |
|  4 |                1 |         2 |
|  5 |                2 |         2 |
|  6 |                3 |         2 |
+----+------------------+-----------+

Finally I need to apply group to the options. Example: Small (items_options_id = 1) relate with Sauces group (group_id = 1)

I hope I have explained well, is this good database design or what could have been improved?

Let assume the following items:

Category: Curries

Item Name: Balti

Option name: Chicken 6.00 (With Extras/Groups)

Option name: Lamb 6.50 (With Extras/Groups)

Option name: Prawns 7.50 (With Extras/Groups)

Item Name: Vindaloo

Option name: Chicken 8.50 (With Extras/Groups)

Option name: Lamb 6.50 (With Extras/Groups)

Option name: Prawns 7.50

As you can see items come with same options name but the prices can be different. You notice Balti:Prawns come with extras but Vindaloo:Prawns don't have one.

Sometime I want to apply extras groups to an item which mean it will apply to all related options.

Sometime, I want to apply extras groups to specific options from an item.

How can I normalize my tables above to allow that?

SQL Dump

CREATE TABLE IF NOT EXISTS `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


INSERT INTO `category` (`id`, `name`) VALUES
(1, 'Burgers'),
(2, 'Pizzas');



CREATE TABLE IF NOT EXISTS `extras` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `price` double(6,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;


INSERT INTO `extras` (`id`, `name`, `price`) VALUES
(1, 'ketchup', 0.00),
(2, 'Mayo', 0.00),
(3, 'Boiled Rice', 0.00),
(4, 'Chips', 0.00);



CREATE TABLE IF NOT EXISTS `groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `subject` varchar(50) NOT NULL,
  `type` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `groups` (`id`, `name`, `subject`, `type`) VALUES
(1, 'Sauces', '', 'tickboxes'),
(2, 'Extras', 'Choose your extras', 'dropdown');



CREATE TABLE IF NOT EXISTS `groups_extras` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL,
  `extra_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;



INSERT INTO `groups_extras` (`id`, `group_id`, `extra_id`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 3),
(4, 2, 4);


CREATE TABLE IF NOT EXISTS `groups_extras_options` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `items_options_id` int(11) NOT NULL,
  `groups_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;



INSERT INTO `groups_extras_options` (`id`, `items_options_id`, `groups_id`) VALUES
(1, 1, 1),
(2, 2, 1),
(3, 3, 1),
(4, 1, 2),
(5, 2, 2),
(6, 3, 2);


CREATE TABLE IF NOT EXISTS `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;


INSERT INTO `items` (`id`, `category_id`, `name`) VALUES
(1, 1, 'Plain Burger');


CREATE TABLE IF NOT EXISTS `items_options` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `price` decimal(6,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;


INSERT INTO `items_options` (`id`, `item_id`, `name`, `price`) VALUES
(1, 1, 'Small', '3.50'),
(2, 1, 'Medium', '4.50'),
(3, 1, 'Large', '5.50');