CODEWITHSUNDEEP
mysqldatabasedatabase-designrelationship
Argh
Argh

Reputation: 31

How to connect and relationship between these two tables?

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

Upvotes: 0

Views: 78

Answers (2)

Terence Tam
Terence Tam

Reputation: 56

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

Upvotes: 1

Freddy Sidauruk
Freddy Sidauruk

Reputation: 292

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 enter image description here

Upvotes: 0

Related Questions