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'm not sure why you need different tables for different product details. I'm going to guess though, that it's because your product types are so different that they have very different attributes (clothing will have sizes: S, M, L whereas books will have have an indicator of hardcover vs. paperback, etc...). You might want to try a more generic solution, where you have a table of product attribute types and another table of attributes. Something like this:
product
-------
product_id
name
product_attribute_type
----------------------
attribute_type_id
attribute_type_name
product_attribute
-----------------
product_attribute_id
attribute_type_id (FK to product_attribute_type)
product_id (FK to product)
value
Then you can have data that looks like this
product
-------
ID | name
-----------
1 | Awesome pants
2 | Book of cool stuff
3 | 1337 software
product_attribute_type
----------------------
ID | attribute_type_name
------------------------
1 | waist size
2 | number of pages
3 | system requirements
4 | inseam
product_attribute
-----------------
attribute_type_id | product_id | value
--------------------------------------
1 | 1 | 33 inches
4 | 1 | 34 inches
3 | 3 | Win7, 4 GB RAM
2 | 2 | 703 pages
Getting info on a product is easy:
select product.name, product_attribute.value, product_attribute_type.name
from product
inner join product_attribute on product.product_id = product_attribute.product_id
inner join product_attribute_type on product_attribute_type.attribute_type_id = product_attribute.attribute_type_id
where product.product_id = $PRODUCT_ID;
Best Answer
(Not a full answer, but too long for a Comment.)
With 200 cities with 'retangular' bounding boxes, virtually anything will do. Even a simple table without a
SPATIAL
index, but withINDEX(latitude), INDEX(longitude)
.3 million cities, on the other hand, needs some serious indexing.
SPATIAL
, as you are asking about, is probably the 'right' way to do it.Here is an efficient way to do it without
SPATIAL
, usingPARTITIONing
and special code instead. (However, the code given is aimed at a different problem, so it is not directly applicable.)