Database Design – Creating Simple Comment System

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.

I did exactly this as a project back in 2008-2009, a web service (billing) that needed to handle 1M+ calls per day. I used SQL Server table as a queue, and the lesson from that project I distilled into the article Using Tables as Queues. Stick to the rules I lay out there, and specially don't try to add any whistles and bells to your table, use it exclusively as a queue. Under load I found that a critical issue was to batch dequeue (dequeue 100 web service call request in one DB operation) and handle the dispatch in the app (place the dequeue request into an in memory list, have the web call handlers pick up work from this list). Doing the web service call async is critical. Also, you must read about the ServicePointManager.DefaultConnectionLimit.