Complex project

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
mosquo
Forum Newbie
Posts: 5
Joined: Fri Aug 15, 2008 10:05 am

Complex project

Post by mosquo »

hey, I am busy with this project, the owner wants users to select which products they want to receive updates, basicly a business adds a product, and users can be notified of that. I am a bit stuck on the design since there is a lot of categories a user can subscribe too , any ideas on what is the best solution?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Complex project

Post by Christopher »

Can you describe more specifically what you are having a problem with?
(#10850)
mosquo
Forum Newbie
Posts: 5
Joined: Fri Aug 15, 2008 10:05 am

Re: Complex project

Post by mosquo »

basicly the layout, if a user chooses 5 different products, from 5 different tables, should I store search queries for each user in a table? right now I have a table for each category example: tblgaming with columns like brand and name, then I must display those products to users who register for gaming,, but maybe they only want xbox only, my idea was to build search string based on their selection and save it in a settings table, since there a lot of categories a user can choose. so from a design point of view, does that sound right? or?
baileylo
Forum Newbie
Posts: 13
Joined: Sun Sep 30, 2007 12:48 am

Re: Complex project

Post by baileylo »

So the user can get information about a genre of products as well as specific ones?
mosquo
Forum Newbie
Posts: 5
Joined: Fri Aug 15, 2008 10:05 am

Re: Complex project

Post by mosquo »

yeah basicly, but there a looooot of categories, so that's why I am wondering wats the most efficient way, maybe creating a table for each user?
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Complex project

Post by allspiritseve »

Ok, let's see if I understand this correctly:

You have many users. Each user can subscribe to many categories. Each category could be subscribed by many users.

This is a many-to-many relationship between users and categories.

You have many users. Each user can subscribe to many products. Each product could be subscribed by many users.

This is a many-to-many relationship between users and products.

You have many categories. Each category can have many products. Each product can belong to one category (could be many, but for now, I'm assuming it's just one).

This is a one-to-many relationship between categories and products.

One-to-many relationships are represented by a foreign key from one table to another. Many-to-many relationships are represented by an intermediate table.

Your database schema should look like this:

Code: Select all

Users
-----
id
 
Categories
----------
id
 
UserCategorySubscriptions
-------------------------
id
user_id
category_id
 
Products
--------
id
category_id
 
UserProductSubscriptions
------------------------
id
user_id
product_id
Now, I'm assuming subscribed products will overwrite subscribed categories. Thus, you can get a list of products a user subscribes to by selecting all products in the categories a user is subscribed to, and adding all the products a user is subscribed to. If you want users to be able to select a category, but decline to subscribe to specific products within that category, you'd need to add a boolean field on UserProductSubscriptions-- maybe "subscribed" that would toggle whether that specific product is subscribed. Thus, subscribing to a category subscribes a user to all products in that category, but they have the ability to select individual products if needed.

If you need help on how to manage all of this with forms, let me know. I just did something similar with user and group permissions, and the tables were laid out in the same way.

Cory
mosquo
Forum Newbie
Posts: 5
Joined: Fri Aug 15, 2008 10:05 am

Re: Complex project

Post by mosquo »

hey thanks for reply. really appreciate it.
ok but I am a bit lost.
firstly I am using mysql, so no foreign key.
I came up with 2 plans from looking at your message.
plan 1.
user logs in and is presented wih for example - category gaming and subcategory -psp-xbox-ps3. ok all products are stored in a tblproducts , one of the columns is category. now lets say the user selects xbox and psp, then clicks ok, then the table tbluserset is filled with userid (not unique) but an unique categoryid. then when the user goes to his homepage , in side a div tag I will search tblproducts using categoryid from tbluserset
so...were categoryid = $categoryid .....and that's it.

plan 2 was just storing the search query, dynamicly made for each category.

so what you think? lame ? or ok? is there any benefits in storing products in different tables rather than one big one? or is there any benefit in creating a table for each user on the fly rather than one big one?
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Complex project

Post by allspiritseve »

A foreign key is a very common thing to use in MySQL. If a parent table "parents" has a field "id", then a child table "children" would have a field "parent_id" that corresponds to the parents.id field. Thus:
SELECT children.* FROM children INNER JOIN parents ON children.parent_id = parents.id WHERE parent_id = :parent_id
This will return all children from a specific parent (substitute :parent_id with an actual id, if you're not familiar with PDO).

There are many benefits to storing data in properly normalized (many) tables, most especially the fact that dissimilar data is separate. If you have all your products in one table, it can be used anywhere you need to store products. You can run different queries on the data using SQL, which is far more powerful than sorting through one big blob of data with PHP. I would be happy to explain more about my database schema I proposed for you, if you could clarify what you don't understand.
mosquo wrote:hey thanks for reply. really appreciate it.
ok but I am a bit lost.
firstly I am using mysql, so no foreign key.
I came up with 2 plans from looking at your message.
plan 1.
user logs in and is presented wih for example - category gaming and subcategory -psp-xbox-ps3. ok all products are stored in a tblproducts , one of the columns is category. now lets say the user selects xbox and psp, then clicks ok, then the table tbluserset is filled with userid (not unique) but an unique categoryid. then when the user goes to his homepage , in side a div tag I will search tblproducts using categoryid from tbluserset
so...were categoryid = $categoryid .....and that's it.

plan 2 was just storing the search query, dynamicly made for each category.

so what you think? lame ? or ok? is there any benefits in storing products in different tables rather than one big one? or is there any benefit in creating a table for each user on the fly rather than one big one?
mosquo
Forum Newbie
Posts: 5
Joined: Fri Aug 15, 2008 10:05 am

Re: Complex project

Post by mosquo »

ok , I get the schema you propose. didn't get the part of having one products table, so having one table is ok? overall I'll have 3 tables, one with users , one with products and one storing their ids? is that right?
don't know why I thought mysql didn't support foreign keys
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Complex project

Post by allspiritseve »

mosquo wrote:basicly the layout, if a user chooses 5 different products, from 5 different tables, should I store search queries for each user in a table? right now I have a table for each category example: tblgaming with columns like brand and name, then I must display those products to users who register for gaming,, but maybe they only want xbox only, my idea was to build search string based on their selection and save it in a settings table, since there a lot of categories a user can choose. so from a design point of view, does that sound right? or?
Instead of a "table for each category" you should have a table with all your categories. If somebody wants a specific product (xbox) only, you need a table for products as well. Since users->categories and users->products are many-to-many associations, you need 5 tables to do the trick. (6, if you want a product to belong to more than one category).
mosquo wrote:ok , I get the schema you propose. didn't get the part of having one products table, so having one table is ok? overall I'll have 3 tables, one with users , one with products and one storing their ids? is that right?
don't know why I thought mysql didn't support foreign keys
What you just said about users, products, and the associations table is correct, but you need the same thing for categories. Hence the 5 tables.
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Complex project

Post by allspiritseve »

Mosquo, have you had any sucess with this?
Post Reply