PDA

View Full Version : Categories and subcategories database and linking


yazer
10-12-2005, 10:03 PM
Hi
I have a products catalog and I need to have a categories and subcats tables
I made:
category table
subcat table
sub subcat table
lookup table..
but I dunno how to use them or link them to the site together to function properly <_<
Is this design correct?
the current one i am using now let's me insert the category only in the product db.. so I really need the subcat!
help please

davidj
10-13-2005, 10:04 AM
relationships with tables. (not girls although both can be a mine field of trouble)

your main table will need an record identifier field (unique) which is normaly a auto incrementing int field

when you populat the other tables you need to carry the relational ID from the first table into the other table

e.g...


table1
+-----------+
auto ID1
field1
field2
field3
+-----------+

table2
+-----------+
ID
fielda
fieldb
fieldc
+-----------+


table1.auto ID1 relates to table2.ID (table1.auto ID1 = table2.ID )

this can get confusing

yazer
10-13-2005, 03:07 PM
This is what I did.. Is it wrong?

CATEGORY TABLE
-----------------------
CREATE TABLE CATEGORY(
cat_name char(50),
cat_id int4,
PRIMARY KEY(cat_id));
-----------------------

SUB_CATEGORY
---------------------------
CREATE TABLE SUB_CATEGORY(
subcat_name char(50),
subcat_id int4,
PRIMARY KEY(subcat_id));
---------------------------

SUB_SUB_CATEGORY
-------------------------------
CREATE TABLE SUB_SUB_CATEGORY(
sub_subcat_name char(50),
sub_subcat_id int4,
PRIMARY KEY(sub_subcat_id));
------------------------------

SUB_CATEGORY_LOOKUP_TABLE
----------------------------------
CREATE TABLE CATEGORY_LOOKUP_TABLE(
look_cat_id int4,
look_subcat_id int4,
look_sub_subcat_id int4,
PRIMARY KEY(look_cat_id,look_subcat_id,look_sub_subcat_id) );

davidj
10-13-2005, 03:09 PM
did the tables and fields create ok