How to store "wishlist" in database?

Question

I am thinking of creating a "wishlist". Each user will be able to purchase wishes in a shop. How do I store the wishlist? I am thinking of creating an user table and a wishlist table.

The structure of user table: Columns: id, username, password etc

Columns: id, wish, price, quantity etc

user id is the primary key for user table and its a foreign key in wishlist table. However, when I come to think of it, my wishlist table will have duplicate items for each user, won't it?

Is there a better way to store the wishlist things?

I am using mysql. Thanks

Answer 1

It is a 1 to n relationship.

Asuming you have a User table with a user_id as primary key then create a table Wish which has user_id as foreign key. Add the attributes you want to this second table and violá.

Luis

Answer 2

You should have another table for wishes (wish id, price, description etc), and you should have a joining table (user id, wish id, quantity, quoted price etc). This table will allow you to have a many-to-many relationship between users and wishes.

(Further to my comment... see? You have two different suggestions already. Ultimately you have to decide what is best, and ideally your business/project requirements should guide you.)

EDIT: make that three different suggestions

Answer 3

You should also have another table to store the purchases:

Purchases

• Id
• UserId
• WishId
• PurchaseDate

Users

• Id
• Name
• Password

Wishes

• Id
• Wish
• Price
• Quantity

Every time a user purchases a wish, you create a record in the Purchases table, and decrement the Quantity count for that wish.

Answer 4

How to get all records of wishlist?