Creating conditional foreign key relationships in MySQL

There's a way to do this, but you're going about your design wrong. The way I see it, you have the following data:

Footwear

Footwear Types

Sole Types

Strap Types

Lace Types

The latter 3 are conditional upon the footwear type. You can go one of two ways:

1. Enforce this relationship through the use of combo-key tables that will allow you to define many-to-many relationships between your subtypes and your footwear type.

This is a very enterprise type of database design, with redundant keys to enforce your complex relationship. The drawback is that it will be significantly slower (not really a big deal unless you're talking 100,000+ records in the footwear table)

Code:

CREATE TABLE footwear_type (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE sole_type (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE lace_type (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE strap_type (
    id INT NOT NULL AUTO_INCREMENT,
    footwear_type_id INT NOT NULL REFERENCES footwear_type(id),
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

-- The following three tables are optional, used only to enforce the combo-key relationship
-- between a footwear type and its subtypes

CREATE TABLE footwear_type_sole_type (
    footwear_type_id INT NOT NULL REFERENCES footwear_type(id),
    sole_type_id INT NOT NULL REFERENCES sole_type(id),
    PRIMARY KEY (footwear_type_id, sole_type_id)
);

CREATE TABLE footwear_type_lace_type (
    footwear_type_id INT NOT NULL REFERENCES footwear_type(id),
    lace_type_id INT NOT NULL REFERENCES lace_type(id),
    PRIMARY KEY (footwear_type_id, lace_type_id)
);

CREATE TABLE footwear_type_strap_type (
    footwear_type_id INT NOT NULL REFERENCES footwear_type(id),
    strap_type_id INT NOT NULL REFERENCES strap_type(id),
    PRIMARY KEY (footwear_type_id, strap_type_id)
);

CREATE TABLE footwear (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    footwear_type_id INT NOT NULL REFERENCES footwear_type(id),
    sole_type_id INT NULL REFERENCES sole_type(id),
    lace_type_id INT NULL REFERENCES lace_type(id),
    strap_type_id INT NULL REFERENCES strap_type(id),
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),

    -- As with the 3 combo-key tables above, the following references to them are optional

    FOREIGN KEY (footwear_type_id, sole_type_id) REFERENCES footwear_type_sole_type (footwear_type_id sole_type_id),
    FOREIGN KEY (footwear_type_id, lace_type_id) REFERENCES footwear_type_lace_type (footwear_type_id lace_type_id),
    FOREIGN KEY (footwear_type_id, strap_type_id) REFERENCES footwear_type_strap_type (footwear_type_id strap_type_id)
);

I'm gonna post solution #2 below in another post (so I don't lose this by accident lol)

(PS - the above would allow you to use NULL in the subtype columns that aren't required... 0 is not the way to go, because it requires you to hack the FK relationship).

2. The second solution to your problem is to use metadata and normalization to define your subtypes.

This is a highly normalized data architecture, allowing the definition of many attributes for your shoes, which will give you a very streamlined database that is highly searchable. For obvious reasons, I prefer this to the enterprise solution for storage of shoe properties.

Code:

CREATE TABLE footwear_type (
    id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO footwear_type (id, name) VALUES (1, 'sneakers'), (2, 'loafers'), (3, 'sandals');

CREATE TABLE property_type (
    id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO property_type (id, name) VALUES (1, 'sole'), (2, 'lace'), (3, 'strap');

-- Edited to add optional enhanced property values with color and material selections

CREATE TABLE color (
    id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO color (id, name) VALUES (1, 'white'), (2, 'black'), (3, 'brown');

CREATE TABLE material (
    id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO material (id, name) VALUES (1, 'leather'), (2, 'rubber'), (3, 'cotton'), (4, 'canvas');

CREATE TABLE property_value (
    id INT NOT NULL AUTO_INCREMENT,
    property_type_id INT NOT NULL REFERENCES property_type(id),
    color_id INT NOT NULL REFERENCES color(id),
    material_id INT NOT NULL REFERENCES material(id),
    description VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

-- The IDs for this table are auto-indexed.

INSERT INTO property_value 
    (property_type_id, color_id, material_id, description) 
VALUES 
    (1, 1, 2, 'white rubber sole'),
    (1, 2, 1, 'black leather sole'),
    (1, 3, 1, 'brown leather sole'),
    (2, 1, 3, 'white cotton laces'),
    (2, 2, 3, 'black cotton laces'),
    (2, 3, 3, 'brown cotton laces'),
    (2, 3, 1, 'dark brown leather laces'),
    (3, 2, 1, 'big black leather straps'),
    (3, 2, 1, 'small black leather straps'),
    (3, 3, 4, 'brown canvas straps');

CREATE TABLE footwear_type_property_value (
    footwear_type_id INT NOT NULL REFERENCES footwear_type(id),
    property_type_id INT NOT NULL REFERENCES property_type(id),
    property_value_id INT NOT NULL REFERENCES property_value(id),
    PRIMARY KEY (footwear_type_id, property_type_id, property_value_id)
);

-- The primary keys of this table are auto indexed.

INSERT INTO footwear_type_property_value 
    (footwear_type_id, property_type_id, property_value_id)
VALUES
    (1, 1, 1), -- links sneakers to white rubber soles
    (1, 2, 4),
    (1, 2, 5),
    (2, 1, 2),  -- links loafers to black leather soles
    (2, 1, 3),
    (2, 2, 5),
    (2, 2, 6),
    (2, 2, 7); -- links loafers to dark brown leather laces

CREATE TABLE footwear (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    footwear_type_id INT NOT NULL REFERENCES footwear_type(id),
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO footwear (footwear_type_id, name) VALUES (2, 'Brown Emeregildo Zegna Loafers');

CREATE TABLE footwear_property (
    footwear_id INT NOT NULL REFERENCES footwear(id),
    property_type_id INT NOT NULL REFERENCES property_type(id),
    property_value_id INT NOT NULL REFERENCES property_value(id),
    PRIMARY KEY (footwear_id, property_type_id)
);

INSERT INTO footwear_property 
    (footwear_id, property_value_id) 
VALUES 
    (1, 1, 3),  -- The Zegnas have brown leather soles
    (1, 2, 7);  -- And dark brown leather laces

The last table would reference each piece of footwear, the type of property it's referencing, and the value for that property type. This is a more elegant solution, because it requires the storage of less data and allows for the addition of properties without the addition of tables, but enforces business rules through metadata and code (programming).

You can ad an auto-incrementing ID to those tables if you remember to create a unique index on the combo of the FKs, like so:

Code:

CREATE TABLE footwear_type_property_value (
    id INT NOT NULL AUTO_INCREMENT,
    footwear_type_id INT NOT NULL REFERENCES footwear_type(id),
    property_type_id INT NOT NULL REFERENCES property_type(id),
    property_value_id INT NOT NULL REFERENCES property_value(id),
    PRIMARY KEY (id),
    UNIQUE (footwear_type_id, property_type_id, property_value_id)
);