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

by

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`

More Related Contents:

  1. 1052: Column ‘id’ in field list is ambiguous
  2. Laravel join with 3 Tables
  3. MySQL query, MAX() + GROUP BY
  4. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  5. Error Code: 2013. Lost connection to MySQL server during query
  6. Join between tables in two different databases?
  7. MySQL Trigger after update only if row has changed
  8. MySQL Query to select data from last week?
  9. Using an Alias in SQL Calculations
  10. Can a table field contain a hyphen?
  11. Adding a leading zero to some values in column in MySQL
  12. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  13. MySQL JOIN the most recent row only?
  14. View’s SELECT contains a subquery in the FROM clause
  15. Check for x consecutive days – given timestamps in database
  16. MySQL Insert Into from one Database in another
  17. How do I get the ID of multiple inserted rows in MySQL?
  18. How to insert data to MySQL with auto-incremented column(field)?
  19. Case statement in MySQL
  20. Replace Into Query Syntax
  21. MySQL Inner Join Query Multiple Tables
  22. Difference between these two joining table approaches?
  23. Remove special characters from a database field
  24. Proper database model for a user feedback system (an interesting case)
  25. MySQL LEFT JOIN 3 tables
  26. MySQL – Replace Character in Columns
  27. FIND_IN_SET with multiple value [duplicate]
  28. How to use a user variables in MySQL LIKE clause?
  29. Selecting distinct 2 columns combination in mysql
  30. MySQL: Computed Column

Leave a Comment