user preferences

[hawashafiq]

hello reader..

i am developing a website.
One of the web's page is PRODUCT page where user can view, or purchase the item. There are 3 categories under PRODUCT which are; pet, gardening and food..

I'd like to implement user preferences in this page. User will pick its preferences via check boxes. For example, if she chose pet and gardening, any item available in mysql about pet & gardening will appear in the user's PRODUCT page. Meanwhile, if the user isn't a registered user, products of food/ pet/ gardening will appear randomly.

The questions are:
1) HOw should i create the table? Stuff all user info and preferences in USER table or make them in chuncks. 4 tables( user, pet, food, gardening)

2) overall, how to code the idea? i browsed tru hundreds of forums, but couldn't find any that suits the idea

Pls help. TQ ;)

[darkagn]

I would have a single table, called user_pref, which would have the columns user_id, product_type_id, both of which would form the primary key for the table. If the user selects pets as a preferred option, add their user id and the pets category's id to the table. If the user then also selects the gardening category, add that to the table to.

When you display the items, check the table for preferences and if there is at least one row for their user id only display items in the categories listed, otherwise display items from all categories.

Hope this helps :)
darkagn

[Josh Connerty]

I would suggest also making a dedicated table for the user preferences. However I would suggest making a column for each preference. Eg. user_id, gardening, pet, food.

As for the pets, gardening and food tables, it is a good idea to make as many tables in your database as possable, this means you wont overstress a single table.

So in a sense, when the user sellects pet and food you will check the table for the users ID if there is already and ID then edit that row else make a new one. The idea is add a 1 into the column the chose, in the instance it is pet and food,

This will make the row look like this:

ID --- Pet --- Gardening --- Food
----------------------------------------
984 1 0 1
======================

So if they have a 1 in any of the topics then you will retreive the rows from the specified tables.

This is the most ligical way to my knowledge.

[hawashafiq]

work as charm!