Hello all - I have a pet project I’ve been working on and I could use some help.
I’ve built a small catalog database and have it working pretty well. The database contains a whole bunch of products, and they fall into multiple type categories. Even have a nifty search function. The next phase of the project is to create a user login area where a user can create his/her own account, and keep track of which of the products they OWN. AND, I also want to keep track of the products the user WANTS (a wish list).
Creating a User login database is simple enough (you’ve got your userID, password, and any other personal info I may collect.) My problem is structuring the db tables to keep track of the products a user owns and wants, in each of the many product categories.
As a simple example, let’s say that User1 OWNS Product#1 of TypeA and Product#5 of TypeB. AND User1 WANTS Product#2 of TypeA and Product#4 of TypeC.
How would I structure my db table to keep track of this users owns and wants?
Any suggestions are greatly appreciated, as my brain is close to melting.
well, as a thought provoker, I would have a look-uptable of all the products and types and a page with them displayed by category for clicking on.
Then, I would have a child table (based on the user [parent] table) to show the items that are either wanted or owned. I would have an identifier col in that table to show ‘wanted’ or ‘owned’. So one table has all records either owned or wanted. Then if they convert a wanted one into owned, only one column in one table needs to be updated.
You would also need your shopping cart tables to store the details of the purchase.
Hmmmm. So, if I read you correctly, you’d suggest that for each user, there is a table that contains all products and categories, with a column to indicate have/want? What if there are a huge number of products, and a user only owns/wants a few? I’m no expert, but that seems like wasted db space.
And, though it’s not relevant to this discussion, the catalog isn’t to be used to purchase items, it’s just a database of collectible items. No e-commerce involved.
That schema is going to make it difficult to add new categories in the future and promote unnecessary replication. I would strongly recommend using a standard m:n relationship between products and categories instead. Depending on whether a single product can be owned by multiple users I would also recommend a m:n relationship between products and users to denote owners.
products
id
title
description
categories
id
category
products_to_categories
products_id
categories_id
products_to_owners
products_id
owners_id
users_wishlist
users_id
products_id
users
id
username
email
pwd
…
Someone is going to come around and recommend synthetic keys, but that is how I would approach it. I wouldn’t make the product title or category name part of the row identity considering its likely to change and problems mapping back to it via an application language and URL arguments.
The only way to support what you would like to do with your current schema is by replicating the same tables for each category or hosting the table the product belongs to specific the category. This is just going to result in a mess, but seems to be best way to support he additonal requirements without modifying the schema though unideal.
wishlist
products_id
category
users_id
owners
products_id
category
owners_id
Queries are likely to be much less efficient and optimal considering the “foreign key” is part foreign key and part reference to a table name, yuck. For that reason of many I would highly advise reconsidering the schema as it not correct for the intended relationships being represented.