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 5 of 5
  1. #1
    Member TheGardener's Avatar
    Join Date
    Jun 2006
    Posts
    62
    Member #
    13332
    The database project I posted about in the conditional foreign key thread continues to evolve. I'm no longer trying to use conditional foreign keys, but issues remain, this I created this new thread. If anyone can give me feedback on the quality of my database design, I would greatly appreciate it.

    Transio made some very helpful posts in the last thread, but the database designs he suggested were best-suited for projects where one set of sub-properties could be applied to all properties in the database. This made me realize that my original shoe example wasn't very good at illustrating how my database needs to work, so in order to clarify things I've prepared this code:

    Code:
    CREATE TABLE person (
        id INT NOT NULL,
        name VARCHAR(255) NOT NULL,
        upper_garment INT NOT NULL,
        lower_garment INT NOT NULL,
        footwear INT NOT NULL,
        PRIMARY KEY (id)
    );
    
    CREATE TABLE footwear_type (
        id INT NOT NULL,
        basetype INT NOT NULL,
        subtype_00 INT,
        subtype_01 INT,
        subtype_02 INT,
        PRIMARY KEY (id)
    );
    
    CREATE TABLE footwear_type_basetype (
        id INT NOT NULL,
        description VARCHAR(255) NOT NULL,
        PRIMARY KEY (id)
    );
    
    INSERT INTO footwear_type_basetype (id, description) VALUES (1, 'sneaker'), (2, 'cowboy boot'), 
    (3, 'jump shoe'), (3, 'roller skate');
    
    -- Note that these footwear types are extremely different from one another
    
    CREATE TABLE footwear_type_subtype00 (
        parent_basetype INT NOT NULL,
        id INT NOT NULL,
        description VARCHAR(255) NOT NULL,
        PRIMARY KEY (parent_basetype, id)
    );
    
    --How this works: The parent_basetype is a foreign key referring to the id of the footwear_type_basetype table. 
    This means that each footwear subtype is associated with a basetype and can only be used with that basetype. 
    Nearly all the subtypes in this database will only be applicable to a single basetype.
    
    INSERT INTO footwear_type_subtypes (parent_basetype, id, description) VALUES (1, 1, 'running treads'), 
    (1, 2, 'basketball treads'), (2, 1, 'brown leather'), 
    (2, 2, 'black leather'), (3, 1, 'single spring'), 
    (3, 2, 'spring cluster'), (4, 1, 'inline wheels'), (4, 2, 'non-inline wheels');
    
    --Now here are tables containing names for subtypes 1 and 2 of the footwear basetypes
    
    CREATE TABLE footwear_type_subtype01
        parent_basetype INT NOT NULL,
        id INT NOT NULL,
        description VARCHAR(255) NOT NULL,
        PRIMARY KEY (parent_basetype, id)
    );
    
    INSERT INTO footwear_type_subtypes (parent_basetype, id, description) VALUES (1, 1, 'cotton laces'), 
    (1, 2, 'nylon laces'), (2, 1, 'steel spur'), (2, 2, 'bronze spur'), (3, 1, 'spring-driven bounce'), 
    (3, 2, 'hydraulic bounce'), (4, 1, 'sleeve wheel bearings'), (4, 2, 'ball bearings');
    
    CREATE TABLE footwear_type_subtype02
        parent_basetype INT NOT NULL,
        id INT NOT NULL,
        description VARCHAR(255) NOT NULL,
        PRIMARY KEY (parent_basetype, id)
    );
    
    INSERT INTO footwear_type_subtypes (parent_basetype, id, description) VALUES (2, 1, 'stitched pattern'), 
    (2, 2, 'embossed pattern'), (3, 1, 'steel chassis'), 
    (3, 2, 'plastic chassis'), (4, 1, 'toe brakes'), 
    (4, 2, 'heel brakes');
    
    --Note that sneakers (basetype 1) have no entries in this table. That's because in this database, sneakers are 
    'simpler' than other shoe types and don't have as many subtypes associated with them.
    
    INSERT INTO footwear_type (id, basetype, subtype00, subtype01, subtype02) VALUES (1, 1, 1, 1, NULL)
    
    -- Sneakers with running treads and cotton laces
    
    INSERT INTO footwear_type (id, basetype, subtype00, subtype01, subtype02) VALUES (2, 2, 1, 2, 1)
    
    -- Cowboy boots with brown leather, bronze spurs and a stitched pattern
    
    INSERT INTO footwear_type (id, basetype, subtype00, subtype01, subtype02) VALUES (3, 4, 1, 2, 2)
    
    -- Roller skates with inline wheels, ball bearings and heel brakes
    As you can see, the objects I'm trying to represent in this database have very different properties, as illustrated by the footwear example. Instead of using conditional foreign keys, I realized that I could have tables of subtypes that are linked back to particular basetypes, which will ensure that a given set of subtypes can only work with one basetype.

    Some basetypes are less complicated than others (sneakers are simpler than other footwear types in this example) so they won't have as many referenced subtypes as others, and their entries in the footwear_type table will have NULL values.

    So my question is: is this a good design? I know I'll be in for serious pain later on if this architecture needs to be revised, so I'd like to iron out all the wrinkles now. Thanks for any advice.

  2.  

  3. #2
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Ok, I just figured out where you're going with this... you're confused on how databases work. Each table should be used to represent a logical piece of data.

    The data that you're representing are as follows:

    1. Footwear Types - this is a recursive structure of types of footwear, broken down into types and subtypes. Subtypes are categorized into the same table, because they contain exactly the same data.

      - Shoes
      - - - Sneakers
      - - - Loafers
      - Boots
      - - - Cowboy Boots
      - - - Etc.
      - Rollerskates
      - - - Inline Skates
      - - - Old School Skates

    2. Footwear Property Types - this is a table that will contain information about footwear properties. E.g.:

      - Lace Type
      - Sole Type
      - Footwear Material
      - Stitch Pattern
      - Wheel Bearing Type
      - Wheel Type
      - Brake Type
      - Spring Type

    3. FootwearTypes - to - Footwear Property Types - If you want, you can create this table that restricts certain property types to certain footwear types. It will reference a Footwear Property Type and a Footwear type. This will be a many-to-many table, allowing each footwear type to have multiple associated property types and vice-versa.

      - Sneakers - to - Lace Type
      - Sneakers - to - Sole Type
      - etc.

    4. Footwear Property Values - This table would contain actual property values, which would reference property types. For example:

      - Cotton Laces
      - Leather Sole
      - Inline Wheels
      - Steel Chassis
      - Steel Spur


    Some other points:

    1. Use names that most accurately describe what you're storing in your table. Laces, soles, straps, springs, and wheels are not "subtypes" - they are "properties" or "specifications". Subtypes of "sneakers" would be "basketball shoes" or "running shoes".

    2. Never represent in structure what should be represented in data. For example, don't have individual tables representing subtypes of data-level type items. What if you add a footwear type? Then you have to create a whole new table for subtypes? Bad design!!

    3. Always simplify your design as much as possible. If you have multiple tables representing similar items (e.g. lace types, sole types, and spring types), find a way to combine them (shoe properties) and use additional data in the table to logically separate them (i.e. a "type" column that lets you know what kind of property you're describing).

    4. Use AUTO_INCREMENT on any primary key that is in a table with dynamic data (e.g. that you will be adding data to as your website grows). Examples of dynamic data are everything in your database. Examples of static data are Countries, States, Languages, Colors (usually), Status Types (active / inactive, etc), or data types that are implicit to your software logic.



    To do this, you can use this modified version of my last example from the last thread:
    Code:
    
    CREATE TABLE footwear_type (
        id INT NOT NULL AUTO_INCREMENT,
        parent_id INT NULL,
        description VARCHAR(255) NOT NULL,
        PRIMARY KEY (id)
    );
    
    ALTER TABLE footwear_type 
        ADD CONSTRAINT FK_footwear_type__recursive_key 
        FOREIGN KEY (parent_id) 
        REFERENCES footwear_type (id)
    
    INSERT INTO footwear_type 
        (id, parent_id, description) 
    VALUES 
        (NULL, 'Shoes'),           -- NULL parent_id means this is a top-level item
        (NULL, 'Boots'), 
        (NULL, 'Rollerskates'),
        (1, 'Sneakers'),            -- parent_id of 1 means this is a sub-type of "Shoes"
        (1, 'Loafers'),
        (2, 'Cowboy Boots'),
        (3, 'Inline Skates'),
        (3, 'Old School Skates');
    
    -- This table will contain information about footwear properties
    -- That will help you categorize them.
    
    CREATE TABLE property_type (
        id INT NOT NULL AUTO_INCREMENT,
        description VARCHAR(255) NOT NULL,
        PRIMARY KEY (id)
    );
    
    INSERT INTO property_type 
        (name) 
    VALUES 
        ('Lace Type'),
        ('Sole Type'),
        ('Footwear Material'),
        ('Stitch Pattern'),
        ('Wheel Bearing Type'),
        ('Wheel Type'),
        ('Brake Type'),
        ('Spring Type');
    
    -- And this table will contain your actual property values
    -- This allows you to put all your similar data elements (footwear properties)
    -- into one table, which is the most logical way to store them,
    -- and simply use relationships to categorize them.
    
    CREATE TABLE property_value (
        id INT NOT NULL AUTO_INCREMENT,
        property_type_id INT NOT NULL REFERENCES property_type(id),
        description VARCHAR(255) NOT NULL,
        PRIMARY KEY (id)
    );
    
    INSERT INTO property_value 
        (property_type_id, description) 
    VALUES 
        (1, 'White Cotton Laces'),
        (1, 'Black Cotton Laces'),
        (1, 'Brown Cotton Laces'),
        (1, 'Dark Brown Leather Laces'),
        (2, 'White Rubber Sole'),
        (2, 'Black Leather Sole'),
        (2, 'Brown Leather Sole'),
        (3, 'Leather Construction'),
        (3, 'Canvas Construction'),
        (3, 'Plastic Construction'),
        (5, 'Sleeve Wheel Bearings'),
        (5, 'Ball Bearings');
    
    -- This table will allow you to search available property types
    -- based upon footwear type
    
    CREATE TABLE footwear_type_property_type (
        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),
        PRIMARY KEY (id),
        UNIQUE (footwear_type_id, property_type_id)
    );
    
    
    -- This table limits specific values to footwear types
    -- It's not necessary, but could allow you to further
    -- Constrain your data (logically.... there are no physical constraints here)
    
    CREATE TABLE footwear_type_property_value (
        id INT NOT NULL AUTO_INCREMENT,
        footwear_type_id INT NOT NULL REFERENCES footwear_type(id),
        property_value_id INT NOT NULL REFERENCES property_value(id),
        PRIMARY KEY (id),
        UNIQUE (footwear_type_id, property_value_id)
    );
    
    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 (5, 'Brown Emeregildo Zegna Loafers');
    
    CREATE TABLE footwear_property (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        footwear_id INT NOT NULL REFERENCES footwear(id),
        property_value_id INT NOT NULL REFERENCES property_value(id),
        PRIMARY KEY (id)
    );
    Try it out and see if you can make it work with this structure.

    Good luck!

  4. #3
    Member TheGardener's Avatar
    Join Date
    Jun 2006
    Posts
    62
    Member #
    13332
    Thanks again! A hierarchical table was exactly what I needed. But I need to be able to climb up the tree from an end node, so I'm using a nested structure like this one:

    http://www.sitepoint.com/article/hie...ata-database/2

    Anyway, my database has gone from 109 tables to 21. Things should be easy from here on out.

  5. #4
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    That's not a good design for data integrity. Recursion is your best bet. Trust me, I do this for a living.

  6. #5
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Nevermind, that example you posted is a recursive heirarchy, the same as I posted above... there is just additional index information added in the left/right columns. That example should work fine for you, just make sure to reference parent by an integer id, and not by name!!!

    PS - if you're implementing an n-tier OO application, you shouldn't have to do any tricky database queries to get your data lineage... you'll be able to traverse it all directly through your objects. That's kind of the whole point of OO. :shrug:


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

database table for shoes

,

why create a shoe database

Click on a term to search for related topics.
All times are GMT -6. The time now is 07:39 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com