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.
Best Answer
In this case you can use INSERT INTO ... SELECT FROM ... ORDER BY DESC LIMIT 1: