Tick boxes and Dropdown

Question

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');

Answer 1

A few points that stand out:

In the table items_options, in your output you have the names like 'Small', 'Medium', 'Large'. If these generic option names are going to be used for different items, as you indicate here:

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.

Then I would change to have an options table, and modify items_options to use the new option_id:

CREATE TABLE IF NOT EXISTS `options` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) NOT NULL,
 PRIMARY KEY (`id`)
)

CREATE TABLE IF NOT EXISTS `items_options` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `item_id` int(11) NOT NULL,
 `option_id` varchar(50) NOT NULL,
 `price` decimal(6,2) NOT NULL,
 PRIMARY KEY (`id`)
)

This way, if you ever change the name of the option, you only have to do so in one row.

Create a new table items_extras to handle this case:

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

CREATE TABLE IF NOT EXISTS `items_extras` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `item_id` int(11) NOT NULL,
 `extra_id` int(11) NOT NULL,
 PRIMARY KEY (`id`)
)

The table groups_extras_options is misleading, because it does not utilize your groups_extras.id column. I would rename the table to items_options_extras, so that it would handle this business decision:

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

CREATE TABLE IF NOT EXISTS `items_options_extras` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `item_option_id` int(11) NOT NULL,
 `extra_id` int(11) NOT NULL,
 PRIMARY KEY (`id`)
)

Now, you should be able to get a list of all extras that belong to all options for a selected item, as well as all extras for the specific option that was selected.

You can further group them as you want by utilizing the groups_extras table.

Using foreign keys would be a good idea, since you are using InnoDB. The idea is to enforce the referential integrity between these tables. As an example, if you add a foreign key on item_option.item_id that references item.id, you let the database handle situations that you cannot have a particular item_option if the item_id does not exist in the item table. You can read more information on how MySQL handles foreign keys here.

---

Disclaimer: This was done quickly, so there might be other things to change. These are the points that stood out the most, though. Also, I changed your id columns to be singular because that is my practice.