How to create one to many relationship between two tables?

Question

I have a database with one table with two columns (name , phone) , I want to create an one to many relationship so that the same person could have many links or emails , I know SQL & PDO and PHP .

For example someone called John and his number is 78747384323 , The same person has many links , So when I click his name in the database I see all his links .

The same person may be inserted more than one time to the database with the same name and number but with different link ,

How to achieve that ? So when I insert him for the first time with name and phone and one link , the name and number are added to the first table but the link to the second table , and when the same person be inserted again with the same name and number with another link , The link is added to his previous links.

I dont know if you know about Foreign keys but start with that if you didnt know. — Loko, Sep 25 '17 at 12:00
https://stackoverflow.com/questions/12402422/how-to-store-a-one-to-many-relation-in-my-sql-database-mysql — , Sep 25 '17 at 12:03
This is actually two questions. One is about database design and the other is about adding records. Do one thing at a time. Start with the design. — Dan Bracuk, Sep 25 '17 at 12:52

AndyP9 · Answer 1 · 2017-09-25T12:30:20.653

As Loko's comment above - you need foreign keys. Basically your 'person' table should have a primary key such as 'personid':

CREATE TABLE person (
  personid int not null auto_increment,
  name varchar(50),
  phone varchar(20),
  primary key (personid)
)

Your email table would then reference this as a foreign key - i.e.

CREATE TABLE email (
  emailid int not null auto_increment,
  emailaddress varchar(100),
  personid int,
  primary key (emailid),
  foreign key (personid)
    references person(personid)

)

(edited to add primary key to email table)

This would buy you a one to many relationship - where one person can have zero to many email addresses (each email address is stamped with their personid 'owner').

So in this example if you wanted to show all email addresses belonging to a certain person and you knew their 'personid' you could do:

SELECT emailaddress FROM email WHERE personid = 1234

If you only knew their name you could do a join:

SELECT email.emailaddress
FROM email
INNER JOIN person ON person.personid = email.personid
WHERE person.name = 'Jon Byjovi'

(edited to show query examples)

score 0 · Answer 2 · answered Sep 25 '17 at 12:05

You should have 2 tables. The first is called users and the second user_details. In your first table you should have a unique field- ex. mobile. Before inserting the name and mobile, you should check if mobile exists. If mobile exists then insert the link and email to another table (user_details). In user_details table there should be a column user_id which'll identify the user to link and email relationship.

How to create one to many relationship between two tables?

2 Answers2