#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Location
    Atlanta, GA
    Posts
    269
    Rep Power
    14
    I am having a problem with setting up the relationships with some of my tables. I am trying to set up a navigation through the site that will eventually lead to store names. For example. Click on Shop, mens apparel, pants, then STORE NAME. I have set up the relations to all the CATEGORY tables such as shop etc, but many of the categories can end up with the same store. Like lets say a store sells both mens apparel and womens apparel?? I dont know how to set up a table that can allow for that. ANy help will be GREATLY appreciated. I am losing hair as we speak.

    Thanks in advance.


    ------------------
    trops.
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    There's more than one way. It depends on your schema. How are your table(s) currently set up?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    36
    Rep Power
    15
    Warning: uninformed beginner responds....

    One simple possibility is an intermediate table with just three columns:
    store, article, and an auto-inc primary key

    so you have, say
    table storearticles
    stores articles key
    ----------------------
    store1 article1 001
    store1 article2 002
    store1 article3 003
    store2 article2 004
    store3 article1 004

    SELECT articles FROM store_articles WHERE stores='store1'
    tells you that store1 carries articles 1, 2 3

    SELECT stores FROM store_articles WHERE articles='article1'
    tells you that article1 is sold at stores 1 and 3.

    Before proceeding, I have just barely started using SQL and am still working on setting up my first DB, so please don't follow this advice until someone with more knowledge weighs in to say whether or not this makes sense.

    Tom
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Location
    Atlanta, GA
    Posts
    269
    Rep Power
    14
    My only dilemma so to speak, is it looks like I am going to have to repeat store names for it to work. Like for each category a store could be associated with it. Well here is an example of my tables.

    Main: Main_id, Name
    Child:Child_id, Name, Main_id
    Child_2:Child_2_id, Name, Child_id,

    so that is the category relationships, faairly simple.

    Now..
    Store: store_id, name of store, url,

    Now here is the dilemma, do I need to create a table that contains the primary keys for all the categories and the store table?

    Like,
    Total:store_id, child_id, Main_id,child_2_id, total_id.

    The thing is I can trace the relationships through all the tables fine, but when it comes down to the stpre names, I need to figure out what table to use the store_id in. How is it going to relate???
    I have been working on a query statement, but that is getting confusing too.

    SELECT name.main, name.child, name.child_2,url.store, name.store
    FROM store, main, child, child_2
    WHERE store_id = 1
    AND main.main_id = child.main_id
    AND child.child_id = child_2.child_id
    AND store.store_id = total.store_id.

    Im going nuts, a little advice would be cool.
    Thanks


    ------------------
    trops.
  8. #5
  9. No Profile Picture
    Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Location
    Colchester, England
    Posts
    131
    Rep Power
    15
    When designing a database take the data at it's top level (flat table / spreadsheet).
    Remove all duplication until you get down to unique data and then lookup the duplicate stuff. This is a very rough way of explaining data normalisation.
    This following article explains it in depth http://support.microsoft.com/support.../Q209/5/34.asp

    A reasonable design for the databse would be to have four tables

    tblStores contains unique storenames
    tblCatgory contains unique category
    tblArticles contains unique articles
    tblMain lookups up each of the other tables.

    So to find a store that does "mens" "shoes"

    SELECT tblStores.Store
    , tblCategory.Category
    , tblArticles.Article
    FROM tblMain
    LEFT JOIN tblStores ON tblMain.StoreLookup = tblStores.StoreID
    LEFT JOIN tblCategory ON tblMain.CategoryLookup = tblCategory.CategoryId
    LEFT JOIN tblArticles ON tblMain.ArticlesLookup = tblArticles.ArticleID
    WHERE tblCategory.Category="Mens" AND tblArticles.Article="Shoes";

    With this design you could create dropdowns that drill down from articles to shopname.

    An example database design with data:


    DROP DATABASE IF EXISTS dbStores;
    CREATE DATABASE dbStores;
    USE dbStores;


    DROP TABLE IF EXISTS tblArticles;

    CREATE TABLE tblArticles (
    ArticleID INT AUTO_INCREMENT NOT NULL,
    Article CHAR (50) NOT NULL,
    KEY (Article),
    KEY (ArticleID),
    PRIMARY KEY (ArticleID)
    );

    INSERT INTO tblArticles VALUES (2,'Shirts');
    INSERT INTO tblArticles VALUES (3,'Shoes');
    INSERT INTO tblArticles VALUES (4,'Coats');


    DROP TABLE IF EXISTS tblCategory;

    CREATE TABLE tblCategory (
    CategoryId INT AUTO_INCREMENT NOT NULL,
    Category CHAR (50) NOT NULL,
    KEY (Category),
    KEY (CategoryId),
    PRIMARY KEY (CategoryId)
    );

    INSERT INTO tblCategory VALUES (1,'Mens');
    INSERT INTO tblCategory VALUES (2,'Womens');
    INSERT INTO tblCategory VALUES (3,'Children');


    DROP TABLE IF EXISTS tblMain;

    CREATE TABLE tblMain (
    ID INT AUTO_INCREMENT NOT NULL,
    StoreLookup INT DEFAULT 0,
    CategoryLookup INT DEFAULT 0,
    ArticlesLookup INT DEFAULT 0,
    KEY (ID),
    PRIMARY KEY (ID)
    );

    INSERT INTO tblMain VALUES (1,1,1,4);
    INSERT INTO tblMain VALUES (2,1,1,2);
    INSERT INTO tblMain VALUES (3,1,1,3);
    INSERT INTO tblMain VALUES (4,2,2,2);
    INSERT INTO tblMain VALUES (5,2,2,3);
    INSERT INTO tblMain VALUES (6,2,2,4);
    INSERT INTO tblMain VALUES (7,3,1,2);
    INSERT INTO tblMain VALUES (8,3,1,3);
    INSERT INTO tblMain VALUES (9,3,1,4);
    INSERT INTO tblMain VALUES (10,3,2,2);
    INSERT INTO tblMain VALUES (11,3,2,3);
    INSERT INTO tblMain VALUES (12,3,2,4);
    INSERT INTO tblMain VALUES (13,3,3,2);
    INSERT INTO tblMain VALUES (14,3,3,3);
    INSERT INTO tblMain VALUES (15,3,3,4);


    DROP TABLE IF EXISTS tblStores;

    CREATE TABLE tblStores (
    StoreID INT AUTO_INCREMENT NOT NULL,
    Store CHAR (50) NOT NULL,
    PRIMARY KEY (StoreID),
    KEY (Store),
    KEY (StoreID)
    );

    INSERT INTO tblStores VALUES (1,'Store1');
    INSERT INTO tblStores VALUES (2,'Store2');
    INSERT INTO tblStores VALUES (3,'Store3');


    Andy J

    [This message has been edited by eaamj01 (edited November 20, 2000).]
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Location
    Atlanta, GA
    Posts
    269
    Rep Power
    14
    Thoroughly appreciated. With that design can I still link a store to many categories?? I guess thats my problem. But the design you suggested is ALOT simpler than the one I was hacking out. Most appreciate.

    Thanks

    ------------------
    trops.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Location
    Atlanta, GA
    Posts
    269
    Rep Power
    14
    Ahhhh, I do understand. Beautiful!! Now my question is this, can I seperate the categories into say three tables, so that I can create some type of navigation with them? Or should I just use one category table? I wnat to display the categories of the stores on the page that corresponds to it. Like if you click on shop, the page will display all the categories under shop. Then if you click on one of those it will continue to expand. I think I can expand on your idea using multiple category tables, but I dont want to get ahead of myself.

    Any thoughts would rock.

    ------------------
    trops.
  14. #8
  15. No Profile Picture
    Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Location
    Colchester, England
    Posts
    131
    Rep Power
    15
    You can keep the design as it is. You need to write some SQL that will display Shop and category from tblShop, tblMain and tblCategory.

    Write the results to an array and then display first shop loop through the array and display each category.

    An alternative is to create dropdowns. The first one contains all shops, the second contains categorys based on the selected shop and the third dropdown articles based on the category selection.

    The page needs to contain a form with three dropdowns which onChange execute the submit button that redisplays the same screen. Each dropdown is populated by a query (the if clause that refers to itself needs to be commented out):

    The query needs to be something like :

    $strSQL="SELECT insert query that will select relevent info for dopdown eg
    ID
    , Category
    FROM tblCategory";

    $strJoin = " WHERE ";

    if ($cboShop)
    {
    $strSQL .= $strJoin."StoreLookup = = '$cboshop'";
    $intWhereClause++;
    $strJoin = " AND ";
    }

    if ($cboCategory)
    {
    $strSQL .= $strJoin."ArticlesLookup = = '$cboArticles'";
    $intWhereClause++;
    $strJoin = " AND ";
    }

    $strSQL .= ";";


    So what we are trying to achieve is on initial display all dropdowns contain everything (values set to null).
    A user selects shop2, the screen redisplays and the category dropdown is populated with ID and category where ShopLookup=ShopID etc.

    This approach to the dropdown will work with whatever order the user selects them.

    I hope this gives you some direction.

    Andy J

Similar Threads

  1. Replies: 5
    Last Post: February 4th, 2004, 01:44 AM
  2. Replies: 8
    Last Post: January 20th, 2004, 08:04 PM
  3. Need help setting up a LAN
    By hastric in forum Networking Help
    Replies: 1
    Last Post: November 3rd, 2003, 09:29 PM
  4. Problem with Font setting??
    By Kang Cyber in forum Photoshop Help
    Replies: 3
    Last Post: October 7th, 2003, 07:20 PM
  5. Need Help with setting up database
    By phpnutt in forum PHP Development
    Replies: 3
    Last Post: February 20th, 2003, 01:21 AM

IMN logo majestic logo threadwatch logo seochat tools logo