Advise on creating relationship in mysql with phpmyadmin

Question

I was wondering if anyone could help me with this as I am novice and very confused

I want to able to assign customer to user (1:M relationship) but I cant figure how to do this in mysql. What I would like to do is in the customer table add the multiple id's of the user and then create a table where you can assign a new customer to a certain user or search how many customer a particular user has etc...

I have been looking at creating relationship but I keep getting error in phpmyadmin

Can anyone advise how to do this properly in the db please

Thanks for all your help

Solution 1

If your problem is creating the relationship in PHPMyAdmin, i should recommend you the following:

• Create the user table with the user_id as PK.
• Create the customer table adding the user_id column and defining it as an INDEX.
• Go to Customer table structure, and click on "View Relations".
• On the user_id field, choose the User_table.user_id.

This way, an user can have zero or many customers and a customer will always belong to a single user.

Solution 2

What about the following

> +Users
> -id
> -email
> -full_name
> 
> 
> +Customers
> -id
> -user_id
> -name
> -phone

So user_id will contain the id of the user which this customer belongs to.

Comments

[user1200819]

Thanks for replying Ofir Baruch. But the problem I can see with this is how do I add multiple user id's to customer -user_id?

[user1200819]

Thanks Randy for your advise this is how I planned to do this but then couldn't figure out how to add multiple id's in user_customer? Because it's an int value right?

[user1200819]

Thanks for the suggestion Jonathan I will try that now thanks

[user1200819]

Thank for the suggestion I will try this now

[Ofir Baruch]

In this case you should use @Randy 's solution.

[Randy]

each new pair of id's becomes a new row.

[user1200819]

Thank you all mt. Schneiders suggestion has worked a treat thanks all!!