SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    intense SQL help needed

    Okay...

    I've got this project that is really making me nervous. I honestly don't know if I can handle it but I'll give it my best. So here goes...please use this page as a reference:
    http://tinyurl.com/22xrq

    (By the way, this relates to this thread.)

    I've got a database set up with "top level" categories such as engine, transmission, undercarriage, etc. It also has subcategories; engine model, flywheel power, etc.

    I need to be able to relate each of these things to a particular product (piece of equipment), in this case, a CATERPILLAR - D6N.

    So for example, this product has these characteristics but another product might not use Engine>Bore. So, I'm wondering what the best way to set up the database would be. In my mind, this is pretty complex because I'm going to have to relate like 4 or 5 table to get the info that I need.

    Here's the structure that I've envisioned:
    # Table structure for table `tblcomp_categories`
    # this is the table containing the "specs" categories such as engine, transmission, etc.
    CREATE TABLE tblcomp_categories (
    comp_cat_id tinyint(3) unsigned NOT NULL auto_increment,
    comp_cat_name varchar(40) default NULL,
    PRIMARY KEY (comp_cat_id)
    ) TYPE=MyISAM;


    # Table structure for table `tblcomp_specs`
    # This would be the individual spec: bore, emgine model, flywheel power, etc.
    # I also need to be able to pull one of these specs and assign it a value (in another table)

    CREATE TABLE tblcomp_specs (
    comp_specs_id smallint(5) unsigned NOT NULL auto_increment,
    comp_specs_name varchar(75) default NULL,
    fk_comp_cat_id tinyint(3) unsigned default NULL,
    PRIMARY KEY (comp_specs_id)
    ) TYPE=MyISAM;



    # Table structure for table `tblproducts`
    # This is the product table

    CREATE TABLE tblproducts (
    prod_id mediumint(8) unsigned NOT NULL auto_increment,
    fk_mfr_id smallint(5) unsigned NOT NULL default '0',
    prod_name varchar(50) NOT NULL default '',
    fk_cat_id smallint(5) unsigned NOT NULL default '0',
    PRIMARY KEY (prod_id),
    KEY fk_mfr_id (fk_mfr_id,fk_cat_id)
    ) TYPE=MyISAM;


    Have I bitten off more than I can chew? Is there a better way?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  2. #2
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would have a "features" table with the various features that could appear for a given piece of equipment. Then I would use what is called an intersection or xref (cross-ref) table that would then have entries in it that link various features to the equipment in an "equipment" table. The features table then could have a few extra fields in it to customize the feature (i.e. desc, size, etc.).

    This could be extended to a third table if a particular feature might have its own subsets of specifications to deal with.

    We are talking many-to-many here to allow flexible, sparse table data.
    Last edited by StephenBauer; May 26, 2004 at 11:18.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    good one, stephen

    creole, it looks like tblcomp_specs is a child of tblcomp_categories

    you might want to make that relationship a many-to-many as well, similar to features, instead of a one-to-many

    or is that what you meant, stephen? replace the one-to-many categories-specs with a many-to-many category-features relationship? or did you mean to leave the one-to-many categories-specs alone and add an additional many-to-many category-features relationship? or perhaps you meant a many-to-many product-features relationship instead?

    creole, you referenced that other thread, what happened to your category hierarchy? it seems to be gone
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm working on it little by little. There's a few things that I'm changing or modifying locally.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  5. #5
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You would want to categorize the "features" table and the feature "specification" table so you could say something like: give me all the spec(s) about the engine of the such and such vehicle.

    In this case, a category lookup table at the feature "specification" level would be needed so you can group things like HP, Torque, Cylinders, etc. under the engine feature.

    Another category lookup table at the feature level itself if you will be having items like starter, radiator, carbeurator(sp?) at the "feature" level (as opposed to being under the "engine" feature) so that you can group those into things like electrical system, cooling system, fuel system, suspension system, etc.

    I guess what it boils down to is to think about the product/specification category as an attribute of the the product/specification item and things seem to visualize more readily. I get the feeling that you were placing too much emphasis on category/sub-category concept in your initial design phase and that was confusing you. Been there, done that by the way!

    Quote Originally Posted by StephenBauer
    I would have a "features" table with the various features that could appear for a given piece of equipment. Then I would use what is called an intersection or xref (cross-ref) table that would then have entries in it that link various features to the equipment in an "equipment" table. The features table then could have a few extra fields in it to customize the feature (i.e. desc, size, etc.).

    This could be extended to a third table if a particular feature might have its own subsets of specifications to deal with.

    We are talking many-to-many here to allow flexible, sparse table data.
    Last edited by StephenBauer; May 26, 2004 at 11:18.

  6. #6
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Stephen...

    So the "features" table you mention in your first post would merely be a lookup table?

    So let's say that a Caterpillar bulldozer has nothing in the Transmission "spec". That entire tree of information would remain blank. But then it DOES have 3 things under Engine.
    Engine
    Engine Model - r937
    Flywheel power - 108 hp

    Engine would be contained in tblcomp_categories while both Engine Model and Flywheel Power would be in tblcomp_specs.

    Then I create a table called tblcomp_specs_lookup which would look like this:
    id -
    fk_comp_id - fk to the comp category
    fk_specs_id - fk to a particular spec
    value - the actual value for that particular spec

    Assuming that the Engine category has an ID of 1, Engine Model and Flywheel Power are 10 and 12 respectively then using the data above a row would look like this:
    1|1|10|r937
    2|1|12|108 hp

    Is this correct?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  7. #7
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I may have over-simplified your taxonomy a bit.

    The bad thing about this schema is that the placing of items can be a little tricky...take "battery" for instance - it is under the engine cover, so should it be:

    - a specification of the "engine" feature
    - a specification of the "electrical system" feature
    - a feature of its own with its own specifications
    ("24V", "1100CCA", etc.)

    If you go with it being a specification under "engine" or "electrical system" AND you want to list the battery's features, then you will be adding another table (something like "spec_specification" or something similar but with a better name).

    Now I see why you are having issues!

    I was partly guessing how deep you want to go as well as how you intend to classify things.

    I think you need to look at a handful of different "products" and all the pertinent information you want (i.e. need!) to put into the database and then group the items and then proceed from there.

    Check out the Caterpillar website "Products" link and look at how they are categorizing their equipment. Think of the dark background entries as "features" and the alternating light-gray background entries as "specifications". I don't know if you will be that detailed with your specifications though.

    I would imagine a lot of the equipment (aka products) in this realm share certain sets of features. For example, a set of blades to choose from each with their own specs. Or a set of engines that are commonly used. An example is small lawn & garden equipment. They almost always use one of a given set of Briggs & Stratton two-stroke engines no matter what the brand of the riding lawnmower, push mower, rototiller, etc. If this is similar with industrical grade equipment, then you may want to "template" out these items (i.e. engine, blade, backhoe scoop) in their own set of tables and have links to them from the products in your database that have that item. There could even be an override field (not in the template tables) to customize the templated item a bit as it relates to that product (i.e. "This engine has custom ceramic headers." or something like that). This type of setup could save a lot of data entry time (since you could make pick-n-choose type data entry screens with an option to add a new template it one is not appropriate).

    Again, it would help to have a more defined taxonomy to work with (without me trying to work it out not knowing what you are carrying).

    I hope I am not adding confusion!!!

    Quote Originally Posted by creole
    Stephen...
    So the "features" table you mention in your first post would merely be a lookup table?

    So let's say that a Caterpillar bulldozer has nothing in the Transmission "spec". That entire tree of information would remain blank. But then it DOES have 3 things under Engine.
    Engine
    Engine Model - r937
    Flywheel power - 108 hp

    Engine would be contained in tblcomp_categories while both Engine Model and Flywheel Power would be in tblcomp_specs.

    Then I create a table called tblcomp_specs_lookup which would look like this:
    id -
    fk_comp_id - fk to the comp category
    fk_specs_id - fk to a particular spec
    value - the actual value for that particular spec

    Assuming that the Engine category has an ID of 1, Engine Model and Flywheel Power are 10 and 12 respectively then using the data above a row would look like this:
    1|1|10|r937
    2|1|12|108 hp

    Is this correct?
    Last edited by StephenBauer; May 26, 2004 at 11:18.

  8. #8
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh my god...

    this is going to be a nightmare. Not your fault...I'm just not sure I'm up to the level of this project.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    andy, it might help if you stop looking at specific characteristics and deal with "things" and "descriptions of things" and "classes" of things in the abstract

    the minute you draw everything as one hierarchy, you're lost, right?

    tha's because the complexity of 3 separate many-to-many relationships does not lend itself well to being drawn in a two-dimensional way using actual engine examples

    if you know what i mean

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Exactly!

    Think about what your clients will need to know about these products (forget about niceties for now) to buy them/service them (or whatever the user of the app linked to the DB will be doing). Then group from the general to the specific.

    --

    "I get the feeling that you were placing too much emphasis on category/sub-category concept in your initial design phase and that was confusing you."

    "I think you need to look at a handful of different "products" and all the pertinent information you want (i.e. need!) to put into the database and then group the items and then proceed from there."

    Quote Originally Posted by r937
    andy, it might help if you stop looking at specific characteristics and deal with "things" and "descriptions of things" and "classes" of things in the abstract

    the minute you draw everything as one hierarchy, you're lost, right?

    tha's because the complexity of 3 separate many-to-many relationships does not lend itself well to being drawn in a two-dimensional way using actual engine examples

    if you know what i mean

    Last edited by StephenBauer; May 26, 2004 at 11:17.

  11. #11
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Let me see if I can explain this better.

    I need to have a directory of products (in my mind, this might be similar to the way DMOZ works). You have top level categories such as Earth-Moving Equipment. If you click that, then you get subcategories such as Bulldozers and Backhoes. Under Bulldozers I display a list of the available equipment. So at this point it would look like this:

    Earth-Moving Equipment
    ----Bulldozers
    --------Caterpillar D6 Bulldozer
    --------Deere and Co. LTD832

    I have a table with company names (Caterpillar, Deere and Co.) and a table with product titles (D6 Bulldozer, LTD832). If I then click a peice of equipment I would be taken to a page which contains a brief description of the piece. I would also have the option to view the "specs" for that piece of equipment.

    When I view the specs page for this piece, I would get a broken down list for each piece ordered by gross category, then by individual subcategory.

    Caterpillar D6 Bulldozer specs page
    ----Engine
    --------Engine Model - r937
    --------Flywheel Power - 180 hp
    ----Transmission
    --------1st gear forward - 2mph

    Engine and Tansmission are both component categories while Engine Model, Flywheel power and 1st gear forward are all individual specs which can be assigned to multiple pices of equipment.

    Does this make more sense?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  12. #12
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StephenBauer
    "I think you need to look at a handful of different "products" and all the pertinent information you want (i.e. need!) to put into the database and then group the items and then proceed from there."
    Stephen, first off, thanks for offering your input. It's appreciated even if not fully understood.

    I'm trying to do this, start with just a few products (about 20 right now) and work from there. The client however is quite impatient and keeps urging me to show him something. I'm already stressed out about this and his questioning doesn't help my stress level. I understand him wanting to see something but it's frustrating because I keep having to put him off and I'm sure he's getting frustrated as well.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    search for "bill of materials" for information on how to design database tables for parts and parts assemblies

    sounds like you have a 2-level category/subcategory hierarchy, and the assemblies are linked to the subcategory
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you were faced with two problems: coming up with a categorization scheme and devising a setup of tables that allows versatile storage while remaining normalized.

    I still think a set of five tables would work with the most flexibility:

    Product
    -xrefProductFeature
    --Feature
    ---xrefFeatureSpecification
    ----Specification

    In this instance, the Product table would be entries like "Caterpillar D6 Bulldozer". So that takes care of the Product down to the Specs. This will still allow for ambiguity in certain item placement (like my aforementioned "battery" example in another post of mine), but as long as you are consistent in your treatment of this, then all should be fine.

    If you need grouping categories above this (like in your example above "Bulldozers"), simply either add a category field to the Product table or another table above the Product table. This can be extended for more heirarchy.

    A setup like this would allow templating too for population of data entry screen items (i.e. dropdown lists, combo boxes, etc.) to ease new product entry. You would want to be able to mark certain entries in the Feature and Specification tables as templated items (i.e. some items may be way too specific to include in data entry screen selection mechanisms). Why templated you ask? Well, if you frequently enter new Caterpillar equipment and say the machines have features that are typically from a discreet set of those items (say engine type or blade types) then the templated entries can be used for data entry selection.
    Last edited by StephenBauer; May 26, 2004 at 11:17.

  15. #15
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I might have something similar on my works site (totalpda) if you click on a product, at the bottom there is a technical spec.

    I can let you have a database design for it if you think it might be similar to what you're looking for.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by StephenBauer
    If you need grouping categories above this ... [add]another table above the Product table. This can be extended for more heirarchy.
    yes, that's what i meant
    Quote Originally Posted by meinselbst
    a 2-level category/subcategory hierarchy, and the assemblies are linked to the subcategory
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dhtmlgod
    I think I might have something similar on my works site (totalpda) if you click on a product, at the bottom there is a technical spec.

    I can let you have a database design for it if you think it might be similar to what you're looking for.
    Really? That might help. While it doesn't look identical, it does seem pretty similar. Are each of those specs individual items in the database that you can assign to a product?

    If so, then send away, if you don't mind that is.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  18. #18
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Creole,

    Check out:

    http://www.sitepoint.com/forums/showthread.php?t=165541

    for cross-ref table usage in a category/subcategory context.
    Last edited by StephenBauer; May 26, 2004 at 11:17.

  19. #19
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay...

    I'm continuing this discussion over here:
    http://www.sitepoint.com/forums/show....php?p=1211701
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes


Bookmarks

Posting Permissions

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