N*n relationship in mysql

hi i have 2 table

product table

id |(primary)
price |

number

an seccound table

user table

id |(primary)
username|

password

every user can have many product and and any product can be belong to many user,i know its nn relationship,and i know i should add one more tbable to do this relation by product id and userid, but i dont know i should set which one as foreign key in user and product table and third table for add nn relation ship…could you guide me? link or something else?i need query to get products- id taht belong to one user and another query for catch users id that ordered one products…

CREATE TABLE products ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT , price INTEGER , number VARCHAR(9) ); CREATE TABLE users ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT , username VARCHAR(9) , passwrd VARCHAR(9) ); CREATE TABLE user_products ( user_id INTEGER NOT NULL , prod_id INTEGER NOT NULL , PRIMARY KEY ( user_id , prod_id ) , FOREIGN KEY ( user_id ) REFERENCES users ( id ) , FOREIGN KEY ( prod_id ) REFERENCES products ( id ) );

1 Like

i maked this query after i maked third table

ALTER TABLE p_u ADD FOREIGN KEY (pr_id) REFERENCES db1.products(id) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE p_u ADD FOREIGN KEY (user_id) REFERENCES db1.users(id) ON DELETE RESTRICT ON UPDATE RESTRICT;

is it right?i maked type of foriegn to RESTRICT,am i right,?

ok ok i got this

its phpmyadmin,i wana make it but its tell me just 1 primary key u can have

please show me your tables the way the currently exist

please run a SHOW CREATE TABLE to generate the table syntax, then copy/paste the text here

1 Like

Table
Create Table

p_u
CREATE TABLE p_u (

pr_id int(11) NOT NULL,

user_id int(11) NOT NULL,

PRIMARY KEY (pr_id,user_id),

KEY user_id (user_id),

CONSTRAINT p_u_ibfk_1 FOREIGN KEY (pr_id) REFERENCES products (id),

CONSTRAINT p_u_ibfk_2 FOREIGN KEY (user_id) REFERENCES users (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci

Table
Create Table

users
CREATE TABLE users (

id int(150) NOT NULL AUTO_INCREMENT,

username varchar(100) COLLATE utf8_persian_ci NOT NULL DEFAULT ‘’,

password varchar(150) COLLATE utf8_persian_ci NOT NULL,

status tinyint(1) DEFAULT NULL,

semat tinyint(1) NOT NULL,

file varchar(60) COLLATE utf8_persian_ci DEFAULT NULL,

type varchar(60) COLLATE utf8_persian_ci DEFAULT NULL,

size int(11) DEFAULT NULL,

PRIMARY KEY (username),

KEY id (id)

) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci

Table
Create Table

products
CREATE TABLE products (

id int(150) NOT NULL AUTO_INCREMENT,

price varchar(150) COLLATE utf8_persian_ci NOT NULL,

brand varchar(50) COLLATE utf8_persian_ci NOT NULL,

title varchar(50) COLLATE utf8_persian_ci DEFAULT NULL,

content text COLLATE utf8_persian_ci,

file varchar(50) COLLATE utf8_persian_ci DEFAULT NULL,

type varchar(50) COLLATE utf8_persian_ci DEFAULT NULL,

size int(11) DEFAULT NULL,

category int(5) NOT NULL,

PRIMARY KEY (id),

UNIQUE KEY title (title),

KEY id (id)

) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci

wanders blindly into thread

Why have ID fields if you’re going to say that title and username are unique? Anyway off point there, back to the actual joining:

Your User ID field is an int(150), but the field in the user_products table is set to int(11). The same is true with your products. This may potentially eventually lead to problems when the value of a user or product does not fit within an int(11).

The user_products table uses a different charset than the other two, but that shouldnt be an issue as you’re only storing numbers.

Other than that… You already have the correct primary key on p_u (a composite key of both fields in the table) so… you’re done?

1 Like

the primary key of p_u table that you posted is correct – a composite, 2-column primary key

1 Like

[quote=“StarLion, post:10, topic:208320, full:true”]
Your User ID field is an int(150), but the field in the user_products table is set to int(11). The same is true with your products. This may potentially eventually lead to problems when the value of a user or product does not fit within an int(11).[/quote]

actually the exact same range of integers will fit into INT, INT(11), INT(150), and INT(937)

the number in parentheses for integers has nothing to do with the size of numbers that it can hold

1 Like

so thanks for ypur points,so awsome,but im not done,i wana make this tables correct and have many to many relation from beetwin products and user tables,1-id of all users that have this products 2- id of all products that haven with this user,could you give query of create this 3 table and query of search,?simple query for create is enough i will add other fields later,i need this for mysql DB,im new on many to many relation

Your table structure already allows you to execute those queries.

1; Your query is SELECT pr_id FROM p_u WHERE user_id = whatevernumber
2; Your query is SELECT user_id FROM p_u WHERE pr_id = whatevernumber.

1 Like

select users.id
from users inner join p_u
on p_u.user_id=users.id
where p_u.pr_id=‘2’ this one helping me for select,

but still i dont know when some value inserting on one table how p_u will update? like i wana query that when user with id=“3” buy product with id =“2” how its happen?

and thanks in advance for he;ping me for my project,thanks for your valuable time

If all you want is the user_id, you dont need to do a join - user_id is already a field in the p_u table.

p_u updates when you insert rows into it, the same as user and products does. When someone buys a product, you need to insert a row into p_u.

1 Like

ouch,i already got that,so thanks mr, i thought i should join again,

so if i need to select product id which myuser have them i can select them from p_u then add them to array whitout joining and select user table and take out other fields,but this make works harder,am i right?

can u explain or give me some link to know,inner join auter join,or some thing like this?

If you want other fields besides the user_id, you WILL need to join. For a known product ID (3):

SELECT user.username FROM users INNER JOIN p_u ON users.user_id = p_u.user_id WHERE p_u.pr_id = 3

1 Like

thanks,

look here "p_u.user_id
" is forign key right? if we use " p_u.user_id=users.user_id " instead of “users.user_id = p_u.user_id” we have same result?