You are missing a JOIN between prices and items.
Your implicit join in the where clause only joins vendors and prices so you get a cartesian join for the result of that join and the items table.
If you had used ANSI JOIN keywords instead, you would have noticed that immediately
You need something like this:
select distinct items.name,
vendors.name,
prices.url,
prices.price
FROM vendors
JOIN prices ON vendors.vid = prices.vid
JOIN items ON items.iid = prices.iid --- this part is missing
WHERE items.iid='3'
ORDER BY prices.price;
I don't know if the join condition is correct, because you didn't tell us which column in prices contains the foreign key to items.
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
Rule of thumb 1: If the table contains stuff that may change (edit, delete or add columns) in the future, it probably belongs to the data level.
Rule of thumb 2: Always have an unique key to every table. It is simpler to edit order records if there is a unique key for each row.
Rule of thumb 3: Rules of thumbs is not always the best way to do things. It always depends...
One way to struct this:
article_type
EDIT 1 ---> You do not need to have drink categories. You can have Burger Big, Drink too much etc as several types. The article is the king with all information gathered. Select article and you have all information you need.
article
EDIT 2 ---> I can see no value (at this point) to have a separate price table. The price should be attached to the article. Which also means one less column in the order table.
order
order_rec
When fetching an order you can sum on-the-fly. No need to save sum at order level.
article_type can be useful if you add more groups of meals to the articles.