First of all, not sure if these are bugs or just skill issues. I’m not good at this so I’m looking for help.
I’m working on an online ordering site for a pizzeria and using mysql. This is in regards to the foreign key issue. I have 2 main tables, 1 called modifiers and 1 called modifier_options. Think of modifiers as the sections of modifiers for a pizza, such as the size, toppings, or extras. The modifier_options will be the options within those sections such as small, medium or large for size, and the different topping of the toppings modifier section. Here’s a reference link. “Choose an option” and “Choose Up To Three Toppings” would be the modifiers, and “Small (14”)" and “Medium(16”)" would be the modifier_options.
Now, here’s what I have for the schema:
Table modifiers {
modifier_id int [primary key, not null, increment]
name varchar(100) [not null]
display_text varchar(100) [not null]
is_required boolean [not null, default: false]
max_selection int [default: null]
modifier_type enum(‘Radio’, ‘Checkbox’) [not null] // radio, checkbox, and whatever else I come up with
default_option_id int [default: null, ref: < modifier_options.option_id] // reference modifier option item
display_order int [not null, default: 0]
is_active boolean [not null, default: false]
is_available boolean [not null, default: false]
description varchar(255) [default: null] // optional description
}
Table modifier_options {
option_id int [primary key, not null, increment]
modifier_id int [not null, ref: > modifiers.modifier_id]
name varchar(100) [not null]
display_text varchar(50) [not null]
is_active boolean [not null, default: false]
is_available boolean [not null, default: false]
base_price mediumint [default: null]
selection_option_id int [default: null, ref: > selection_option.selection_option_id]
}
modifiers.default_option_id is referencing a modifier_options.option_id, and it would serve the purpose of having a possible default selectiong, such as the Small being the default size in the link above. The problem is that when I go to export the schema to mysql it tries to do this:
ALTER TABLE modifier_options ADD FOREIGN KEY (option_id) REFERENCES modifiers (default_option_id);
This doesn’t make sense in this context because modifier_options.option_id isnt referencing anyting. This is only a one way relationship where modifiers.default_option_id references modifier_options.option_id. Did I just do this wrong?
Also, in the modifiers table, modifier_type enum(‘Radio’, ‘Checkbox’) [not null] has quotes around the enums but when exporting it removes then and looks like this modifier_type enum(Radio,Checkbox) NOT NULL which doesnt work.
Any help is appreciated, and keep in mind this is my first time attempting to design a database and working with something like this.