I'm hoping to get a consensus about what db schema is best for my situation to store 'type' information for Widgets in a table. A Widget can have only one type but that type can be either a Preset-Type OR a Custom-Type. I obviously create the preset types and a user would create a custom type.
I'll be using MySQL and INNODB on a server. I'll also be using SQLite to store the same info on an App. But we'll just talk about the server here. I'm an app programmer, not a DB admin, but want to get the DBs right for this project the first time and normalized within reason.
In my searching on whether or not I should use nulls for foreign keys I've come across the following answers from people who have much more DB experience than I do.
- "Of course Nulls are ok in foreign keys and elsewhere."
- "NULLs in foreign keys are perfectly acceptable."
- "One area where NULLs are indispensable is in foreign keys."
- "Nulls should almost never be used, especially in foreign keys."
- "Nulls should never be used anywhere."
- "Having a column with lots of NULL values often indicates a need for (further) normalization."
I need to know if it's bad practice to use Nulls in the particular case of Model #2 and which model is preferable and why. Or possibly suggest a better model. Thanks for any input.
Model #1
Have one 'types' table for both Preset and Custom Types. I do this by pre-populating the 'types' table with preset types and leaving around 1500 reserved spaces for future Preset-types that I could later add.
Pros: Easy, no extra tables, no joins, probably the fastest option, and prob less db space in the long run (4 byte type_id). And widgets table type_id FK will never be NULL.
Cons: Probably not good normalization practice to mix preset and custom types together since presets don't need some fields like 'account_id', etc. If i ever wanted more than 1500 presets (highly unlikely) I'd need to figure something else out. This model also uses sentinel/placeholder values in the types table for presets and reserved preset spots.
CREATE TABLE accounts (
account_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
# Other Columns...,
PRIMARY KEY (account_id)
);
CREATE TABLE widgets (
widget_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
account_id INT UNSIGNED NOT NULL,
type_id INT UNSIGNED NOT NULL,
PRIMARY KEY (widget_id),
FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE,
FOREIGN KEY (type_id) REFERENCES types(type_id)
);
CREATE TABLE types (
type_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
account_id INT UNSIGNED NOT NULL,
name VARCHAR(100) NOT NULL,
PRIMARY KEY (type_id),
FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);
Model #2
Separate widget-type table for preset and custom types. 'widgets' table has nullable FK field for preset type and custom type. A Check constraint makes sure one of them is null and the other is not.
Pros: only 1 extra table in the DB. No sentinal/placeholder values except maybe a nulled FK. Don't need to reserve preset value spaces and no limit to future preset type additons.
Cons: Uses one FK null per record in widgets table for either preset_type_id or custom_type_id.
CREATE TABLE accounts (
account_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
# Other Columns...,
PRIMARY KEY (account_id)
);
CREATE TABLE widgets (
widget_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
account_id INT UNSIGNED NOT NULL,
preset_type_id INT UNSIGNED DEFAULT NULL,
custom_type_id INT UNSIGNED DEFAULT NULL,
PRIMARY KEY (widget_id),
FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE,
FOREIGN KEY (preset_type_id) REFERENCES preset_types(preset_type_id),
FOREIGN KEY (custom_type_id) REFERENCES custom_types(custom_type_id),
CHECK ((preset_type_id IS NOT NULL AND custom_type_id IS NULL) OR (preset_type_id IS NULL AND custom_type_id IS NOT NULL) )
);
CREATE TABLE preset_types (
preset_type_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
name VARCHAR(100) NOT NULL,
PRIMARY KEY (preset_type_id)
);
CREATE TABLE custom_types (
custom_type_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
account_id INT UNSIGNED NOT NULL,
name VARCHAR(100) NOT NULL,
PRIMARY KEY (custom_type_id),
FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);
Model #3
Uses intermediary tables widget_preset_types and widget_custom_types. If a widget has a preset type it will be referenced in the widget_preset_types table, alternatively if the widget has a custom type it will be referenced in the widget_custom_types table.
Pros: Probably the most normalized model. Never uses Nulls or FK Nulls. No sentinal/placehodler values used.
Cons: Adds 3 extra tables in the DB just to determine widget-type. I have other things besides widgets in my DB with custom/preset types which means I could be adding at least 12 extra tables to my DB with this model. Is it over-normalized? I'll have to use some type of join to get all widget info and type info from 3 tables at the same time. I'll have to check whether or not a custom_type_id or preset_type_id comes back in the join probably using more code than I would have used checking for nulls in Model#2. Probably slower than Model 1 & 2. More tables means more indexes means more ram.
CREATE TABLE accounts (
account_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
# Other Columns...,
PRIMARY KEY (account_id)
);
CREATE TABLE widgets (
widget_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
account_id INT UNSIGNED NOT NULL
PRIMARY KEY (widget_id),
FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE
);
CREATE TABLE preset_types (
preset_type_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
name VARCHAR(100) NOT NULL,
PRIMARY KEY (preset_type_id)
);
CREATE TABLE custom_types (
custom_type_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
account_id INT UNSIGNED NOT NULL,
name VARCHAR(100) NOT NULL,
PRIMARY KEY (custom_type_id),
FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE
);
CREATE TABLE widget_preset_types (
widget_id INT UNSIGNED NOT NULL UNIQUE,
preset_type_id INT UNSIGNED NOT NULL,
PRIMARY KEY (widget_id),
FOREIGN KEY (widget_id) REFERENCES widgets(widget_id) ON DELETE CASCADE,
FOREIGN KEY (preset_type_id) REFERENCES preset_types(preset_type_id)
);
CREATE TABLE widget_custom_types (
widget_id INT UNSIGNED NOT NULL UNIQUE,
custom_type_id INT UNSIGNED NOT NULL,
PRIMARY KEY (widget_id),
FOREIGN KEY (widget_id) REFERENCES widgets(widget_id) ON DELETE CASCADE,
FOREIGN KEY (custom_type_id) REFERENCES custom_types(custom_type_id)
);
