Complex project
Moderator: General Moderators
Complex project
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?
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Complex project
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?
Re: Complex project
So the user can get information about a genre of products as well as specific ones?
Re: Complex project
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?
- allspiritseve
- DevNet Resident
- Posts: 1174
- Joined: Thu Mar 06, 2008 8:23 am
- Location: Ann Arbor, MI (USA)
Re: Complex project
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:
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
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_idIf 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
Re: Complex project
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?
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?
- allspiritseve
- DevNet Resident
- Posts: 1174
- Joined: Thu Mar 06, 2008 8:23 am
- Location: Ann Arbor, MI (USA)
Re: Complex project
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:
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.
This will return all children from a specific parent (substitute :parent_id with an actual id, if you're not familiar with PDO).SELECT children.* FROM children INNER JOIN parents ON children.parent_id = parents.id WHERE parent_id = :parent_id
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?
Re: Complex project
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
don't know why I thought mysql didn't support foreign keys
- allspiritseve
- DevNet Resident
- Posts: 1174
- Joined: Thu Mar 06, 2008 8:23 am
- Location: Ann Arbor, MI (USA)
Re: Complex project
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: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?
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.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
- allspiritseve
- DevNet Resident
- Posts: 1174
- Joined: Thu Mar 06, 2008 8:23 am
- Location: Ann Arbor, MI (USA)
Re: Complex project
Mosquo, have you had any sucess with this?