data design :: products belonging to more than 1 category

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

data design :: products belonging to more than 1 category

Post by kendall »

hello,

i have an instance where i have a product that falls into more than one category. Now this is strange to me so i am just looking for advice on the approach to designing this. As there is a main category and the other category is more for a searching tool that allows the visitor to view products based on different criteria.

Kendall
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

if you are talking about databases:

there is a relation [product] n ------ m [category]


this can't be modelled, and therefore they use a helpentity, which results in :
[product] 1 ---- n [productcategory] n ------- 1 [category]
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

Timvw, yes i was talking about databases. However i can't create a relationship between the main and other category cause...there isnt any
relation to it the relation of the category exist between themselves and the product
product1 --- main category1
product1 -- category2 ---
product2 -- main category1
product2 -- category3
BUT
main category1 != category2
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Hello Kendall,

I would have a "search table", as well as standard product/cat tables (e.g search_prod2cats)
only 2 columns are required prod_ref and cat_ref. A product can be in the table multiple times, as can a category. This can then be used as a join in the search SELECT.

Another option is to have "product keywords" as a string in a new product table column and search using like '% word %'.

Given the choice however I would go with the first one.
Post Reply