Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Have a look at the image below:

enter image description here

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');
share|improve this question
    
Still no reply?! –  I'll-Be-Back Feb 13 '12 at 17:58
    
Not a full answer, but to account for the Prawns example above, you could have a boolean field that indicates "DefaultBuildOnly" or something that disallows options with that particular base item. –  JNK Feb 13 '12 at 18:11
    
There is no need for boolean field. Check groups_extras_options result. It apply item to the group's (item to many group's) –  I'll-Be-Back Feb 13 '12 at 18:18
    
Then why ask about it if you already know how you want to handle it? I was assuming you had other relationships you needed to keep mapped there that conflicted with removing options from particular items. –  JNK Feb 13 '12 at 18:20
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks you very much for detailed response. Just two questions. items_extras and items_options_extras tables you have provided. I think you meant extra_id to be groups_id? –  I'll-Be-Back Feb 13 '12 at 22:57
    
Second question: If multiple items have same options name and if I've rename the option name from the backend (eg: Donner to Donner Special) - I don't want all items that have same Option name to be replaced.. –  I'll-Be-Back Feb 13 '12 at 23:02
    
@user791022 This is what I don't understand. Why would you want 'groups_id' in a table with the name 'extras'. No, I meant 'extras_id'. I am picturing that the individual extras are mapped to items and items_options. But they are ALSO mapped to groups. Two different things. –  Derek Downey Feb 13 '12 at 23:31
    
extra_id's (ketchup, Mayo, etc) already mapped into group (see groups and groups_extras) - so therefore use groups_id to apply in items_options_extras or items_options_extras –  I'll-Be-Back Feb 13 '12 at 23:43
    
By mapping a 'groups_id' to the items_extras or items_options_extras, that would mean ALL extras in that group are applied to that item or item_option. If that's what you want, ok. My method allows you to have each extra apply to item or item_option, as well as grouped the way you want. –  Derek Downey Feb 14 '12 at 0:06
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.