Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to store a one to many relation in MySQL database?

I'm making a website and I need to store a random number of data in my database.

For example, User john may have one phone number where jack can have 3.

I need to be able to store an infinite number of values per user.

like image 605
Weacked Avatar asked Sep 13 '12 08:09

Weacked


People also ask

How do you store a one-to-many relationship in a database?

To define a one-to-many relationship between two tables, the child table has to reference a row on the parent table. The steps required to define it are: Add a column to the child table that will store the value of the primary identifier.

How do I create a one-to-many relationship in mysql?

Click on the appropriate tool for the type of relationship you wish to create. If you are creating a one-to-many relationship, first click the table that is on the “many” side of the relationship, then on the table containing the referenced key. This creates a column in the table on the many side of the relationship.

How do you maintain one-to-many relationship in SQL?

How to implement one-to-many relationships when designing a database: Create two tables (table 1 and table 2) with their own primary keys. Add a foreign key on a column in table 1 based on the primary key of table 2. This will mean that table 1 can have one or more records related to a single record in table 2.

How do you represent a one-to-many relationship?

In a one-to-many relationship, one record in a table can be associated with one or more records in another table. For example, each customer can have many sales orders. In this example the primary key field in the Customers table, Customer ID, is designed to contain unique values.


1 Answers

You create a separate table for phone numbers (i.e. a 1:M relationship).

create table `users` (
  `id` int unsigned not null auto_increment,
  `name` varchar(100) not null,
  primary key(`id`)
);

create table `phone_numbers` (
  `id` int unsigned not null auto_increment,
  `user_id` int unsigned not null,
  `phone_number` varchar(25) not null,
  index pn_user_index(`user_id`),
  foreign key (`user_id`) references users(`id`) on delete cascade,
  primary key(`id`)
);

Now you can, in an easily manner, get a users phone numbers with a simple join;

select
  pn.`phone_number`
from
  `users` as u,
  `phone_numbers` as pn
where
  u.`name`='John'
  and
  pn.`user_id`=u.`id`
like image 172
Björn Avatar answered Oct 29 '22 05:10

Björn