How to connect and relationship between these two tables?

Question

I have two tables:

users (user_id, user_name, user_email, user_pass)
&
toys (name, box)

I need each user to have their own toys table. Basically the table for users is for my login form and the toys is where the user choose the toys once they login.

Toys table

So maybe adding a foreign key to users and toys might help... — ryekayo, Feb 20 '16 at 01:42
Possible duplicate of [Database Design - Linking two users](http://stackoverflow.com/questions/20460198/database-design-linking-two-users) — Basil Bourque, Feb 20 '16 at 06:08
Duplicate Questions with Answers regarding many-to-many relationship database designs: [this](http://stackoverflow.com/q/20460198/642706), [this](http://stackoverflow.com/q/20526995/642706), [this](http://stackoverflow.com/q/31620869/642706), [this](http://stackoverflow.com/q/8158175/642706), [this](http://stackoverflow.com/q/34568563/642706), and [this](http://stackoverflow.com/q/22618161/642706). — Basil Bourque, Feb 20 '16 at 06:11

Terence Tam · Answer 1 · 2016-02-20T05:48:51.643

1

Add an extra table that have FK (foreign keys) to both Users and Toys, that acts as the table for the many-to-many relation

i.e. create a table called user_toys that has toy_id and user_id combination per row. Then if you want to get all the toy names for a particular user, you can just do

SELECT t.name 
FROM Toys t,
     User_toys relation,
WHERE relation.toy_id = t.toy_id
AND relation.user_id = :user_id

(Note, you don't need to join to the Users table if you already have the user_id in the relation table)

If each toy can only belong to one user (1-to-many), then adding an extra column for user_id FK on the Toys table would suffice. But sounds like from your question each user have their independent set of toys to pick.

reference: https://en.wikipedia.org/wiki/Associative_entity

edited Feb 20 '16 at 05:48

answered Feb 20 '16 at 03:49

Terence Tam

56
5

1

More specifically, this is called a junction table – Terence Tam Feb 20 '16 at 03:53
Sometimes called a "bridge table". – Basil Bourque Feb 20 '16 at 06:02
hi, thank you for your reply. Yes each user have a ton of toys to pick. im still confused. – Argh Feb 20 '16 at 06:08
Sure @Argh, what in particular are you confused about? – Terence Tam Feb 20 '16 at 07:39

score 0 · Answer 2 · answered Feb 20 '16 at 05:13

hello you need one field in table Toy to make your table become relation, here is my schema based on yours

here is toys

+--------+-------------+--------------+-----+
| id_box | id_box_user |   id name    | box |
+--------+-------------+--------------+-----+
|      1 |           1 | Name toys 1  | box |
|      2 |           2 | Name toys 3  | box |
+--------+-------------+--------------+-----+

then this is user

+---------+-------------+------------+-----------+
| user_id |  user_name  | user_email | user_pass |
+---------+-------------+------------+-----------+
|       1 | user name 1 | email      | pass      |
|       2 | user name 2 | email      | pass      |
+---------+-------------+------------+-----------+

and you need query like this to get all data with spesific user

SELECT * FROM user a, toys b where a.user_id=b.id_box_user_id

but for spesific user use this

SELECT * FROM user a, toys b where a.user_id=b.id_box_user_id and a.user_id='variable which store session'

and this is the screen shot

How to connect and relationship between these two tables?

2 Answers2