ikon32
asked on
how to structure one to many in mysql with php
Hi.
Need to get some advice on the best way to store/structure (one to many) in mysql using php.
I have a products table like this:
p_id p_price p_name p_supplier
I created a seperate table to store category names:
id_auto_increment category_name
my problem is with the category, a product can be assign in to many categories so what is the best way and how to store the products in the categories?
Need to get some advice on the best way to store/structure (one to many) in mysql using php.
I have a products table like this:
p_id p_price p_name p_supplier
I created a seperate table to store category names:
id_auto_increment category_name
my problem is with the category, a product can be assign in to many categories so what is the best way and how to store the products in the categories?
ASKER
could you pls explain what *PK and fk, stands for
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
*PK: primary key
When you setup the primary key in the ProductCategories table, you will set up two fields as the primary key. This guarantees that the combination of the two fields will be unique.
You will have a one-to-many relationship between the Products table and hte ProductCategories table, respectively. You will also have a one-to-many relationship between the Categories table and hte ProductCategories table. This will give you the many-to-many relationship that you are looking for.
If you need more info, google junction tables or mapping tables.
Hope this helps.
When you setup the primary key in the ProductCategories table, you will set up two fields as the primary key. This guarantees that the combination of the two fields will be unique.
You will have a one-to-many relationship between the Products table and hte ProductCategories table, respectively. You will also have a one-to-many relationship between the Categories table and hte ProductCategories table. This will give you the many-to-many relationship that you are looking for.
If you need more info, google junction tables or mapping tables.
Hope this helps.
ASKER
thanks rfportilla, will do
Products
--------------------------
p_id *PK
p_price
p_name
p_supplier
Categories
--------------------------
c_id *PK
category_name
ProductCategories
--------------------------
p_id *PK (fk to Products)
c_id *PK (fk to Categories)