Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 7 of 7
  1. #1
    Member TheGardener's Avatar
    Join Date
    Jun 2006
    Posts
    62
    Member #
    13332
    I'm trying to create a database with tables where Foreign Keys Y and Z link to different tables depending on the value of Foreign Key X. Here's a pseudocode example:

    Table Footwear
    ID bigint;
    footwearType int;
    subtype00 int;
    subtype01 int;

    The footwearType column is a foreign key referring to this name table:

    Table FootwearTypes
    1 - Sneaker
    2 - Loafer
    3 - Sandal

    If the value of footwearType is 1, the columns subtype00 and subtype01 will link to the tables Sneaker_LaceTypes and Sneaker_SoleTypes. These tables contain names of attributes that are specific to sneakers.

    If the value of footwearType is 2, the column subtype00 will link to the name table Loafer_SoleType. subtype01 will be left at value 0 and will not be used.

    If the value of footwearType is 3, the column subtype00 will link the the name table Sandal_StrapType. subtype01 will be left at value 0 and will not be used.

    So, as you can see, I'm trying to use this FootwearType table to represent different kinds of footwear with these variable foreign key relationships. Different name tables are referenced with the subtype variables depending on the type of footwear represented. Some footwear types have two subtypes, and other footwear types are simpler and only have one subtype.

    Does anyone know an elegant way to code this? My current test database uses a huge PHP switch that checks the value of the base type column and uses that to determine which name tables to reference for the subtypes, but that

    I've heard that stored MySQL procedures may work well for this, but those are tough to use and documentation is scarce. Does anyone have other ideas? Thanks.

  2.  

  3. #2
    Member
    Join Date
    Apr 2007
    Posts
    96
    Member #
    15165
    Short answer...

    There is no elegant way. The way you are doing it is what a lot of newer OO modeled software layers are doing to map to multiple foreign tables.

    In database terms, the way it should probably be done is to not have 1 giant table to try and hold everything. You would just have a sneaker table, a loafer table and 1 column would only relate to one foreign key. But OO programmers do not find that very flexible.

  4. #3
    Member TheGardener's Avatar
    Join Date
    Jun 2006
    Posts
    62
    Member #
    13332
    Yeah, breaking it into multiple tables isn't really an option for me. My example is just a stand-in for something a lot more complicated; in my application, many small attribute tables like the footwear table are linked from a master table. There are already more than 100 tables total in the database, so making even more tables would be a real problem.

    Do you know a PHP OO compatability layer that deals with conditional foreign keys well? I was looking at the Propel system, but it doesn't seem to have that functionality.

  5. #4
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    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)

  6. #5
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    (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).

  7. #6
    Member TheGardener's Avatar
    Join Date
    Jun 2006
    Posts
    62
    Member #
    13332
    Whoa... awesome, Transio! This is incredibly helpful. I guess it would be possible to add an additional ID column to thefootwear_type_property_value and footwear_property tables if I needed to, right? I'm looking into using CakePHP as a framework and it doesn't support composite IDs. It seems that some people consider composite keys to be a relational database thing that are out of place within an object-oriented framework.

  8. #7
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    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)
    );


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Search tags for this page

conditional foreign key

,
create foreign key mysql condition
,
how to create a subtype table in mysql
,
mysql relationships
,
primary key or foreign key relationships mysql
,
sql auto increment conditional foreign
,
sql conditional relationship
Click on a term to search for related topics.
All times are GMT -6. The time now is 06:49 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com