November 19th, 2000, 03:14 AM
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.
November 19th, 2000, 06:47 AM
There's more than one way. It depends on your schema. How are your table(s) currently set up?
November 19th, 2000, 01:37 PM
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
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.
November 19th, 2000, 02:05 PM
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.
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?
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.
November 20th, 2000, 05:33 AM
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"
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;
DROP TABLE IF EXISTS tblArticles;
CREATE TABLE tblArticles (
ArticleID INT AUTO_INCREMENT NOT NULL,
Article CHAR (50) NOT NULL,
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,
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,
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),
INSERT INTO tblStores VALUES (1,'Store1');
INSERT INTO tblStores VALUES (2,'Store2');
INSERT INTO tblStores VALUES (3,'Store3');
[This message has been edited by eaamj01 (edited November 20, 2000).]
November 20th, 2000, 01:50 PM
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.
November 20th, 2000, 02:05 PM
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.
November 21st, 2000, 04:54 AM
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
$strJoin = " WHERE ";
$strSQL .= $strJoin."StoreLookup = = '$cboshop'";
$strJoin = " AND ";
$strSQL .= $strJoin."ArticlesLookup = = '$cboArticles'";
$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.