What is a suitable DB structure of my problem?

There will be many different pages with products.

The data of products are got from the database(id, name, description...) If a user likes a product, he will be able to click on a button named like "add this" and then the button will be changed to "added".

A user has the option to add several products on his "wish list". A product that he selected earlier will be presented like "added".

As a beginner, I know that there must be a way to have the user_id with the product_id in a row when that product was added.

As for now I have the products table. I guess I have to cre开发者_运维百科ate another table that will save the user_id with the product_id?

Thank you for your ideas, hoping that I will clear up my mind.

---

You're on the right track. You need a table which relates users to the products they have selected:

CREATE TABLE selected_products
(
   choice_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   user_id INT NOT NULL,
   product_id INT NOT NULL,
   FOREIGN KEY (user_id) REFERENCES users (user_id),
   FOREIGN KEY (product_id) REFERENCES products (product_id)
) ENGINE=InnoDB;

Retrieve user choices this way:

SELECT products.product_id, products.description 
FROM products JOIN selected_products ON products.product_id = selected_products.product_id
WHERE user_id = '$someuserid';

When a user wishes to remove the product from his selections, you only need to delete a row:

DELETE FROM selected_products WHERE user_id = '$someuserid' AND product_id = '$product_id';

Your wishlist table functions exactly the same way as your selected_products table. Same structure, same relationships. You might also consider adding another column to indicate the timestamp when the product was added:

date_added TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

---

I would do it this way:

Create a table called user_products add (at least) these columns: id (primary key) user_id (foreign key to your users table) product_id (foreign key to the products table)

Insert a row to this table every time a product is liked.

---

1. products

id | name | description | ...

2. users

id | name | email | ...

3. wishlists

id | name | ...

4. users_wishlists

user_id | wishlist_id | product_id

---

User Table - UserID (+ other user information)

Product Table - ProdID (+ other product information)

UserFavs Table - UserID, ProdID (+ other "faving" info eg date, refer link...)

---

I expect there will be others too -

UserWishList Table - UserID, ProdID (+ other wish info)

UserOwned Table - UserID, ProdID (+ other owned info)

---

This makes it clear you could have one table that relates users to products and describes that relationship, for example

UserProduct Table - UserID, ProdID, Relationship (one of owned, wished, favorite), (+ other relationship info)

Doing it with one table is a design choice which should be based on how your system is used. If your system is about the relationship then one table will make sense. If your system is about the user and the "lists" are seperate then seperate tables will make more sense.

---

You should use a reference table for that.

Make sure you use InnoDB for your tables, so you can use real Foreign Keys

The reference table will mostly contain KEYS of referred tables and maybe some relevant meta data. Easy example:

CREATE  TABLE `stackoverflow`.`users` (
`id` INT NOT NULL AUTO_INCREMENT ,
`username` VARCHAR(45) NOT NULL ,
`fname` VARCHAR(45) NOT NULL ,
`lname` VARCHAR(45) NOT NULL , 
`cdate` DATETIME NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;

CREATE  TABLE `stackoverflow`.`products` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NOT NULL ,
`price` INT NOT NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;

Now the reference table with real FKs:

CREATE  TABLE `stackoverflow`.`user_likes_product` (
`id` INT NOT NULL AUTO_INCREMENT ,
`id_user` INT NOT NULL ,
`id_product` INT NOT NULL ,
`time` DATETIME NOT NULL ,
PRIMARY KEY (`id`) ,
INDEX `fk_user` (`id_user` ASC) ,
INDEX `fk_product` (`id_product` ASC) ,
CONSTRAINT `fk_user`
FOREIGN KEY (`id_user` )
REFERENCES `stackoverflow`.`users` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_product`
FOREIGN KEY (`id_product` )
REFERENCES `stackoverflow`.`products` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

Another advice for starting to work with mysql (if you are on Windows): Download and use Mysql Workbench.

---

User

id | ...

Wishlist

id | user_id | ...

Wishlist_Product

wishlist_id | product_id | ...

Product

id | ...

This is basically what you've in online shops like Amazon. A User can own multiple Wishlists. A Wishlist can contain many Products and each Product can be on many Wishlists.

---

If you want to have only one Wishlist per User then you can go even simpler:

User

id | ...

wishes

user_id | product_id | ...

Product

id | ...