17

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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Weacked
  • 954
  • 2
  • 7
  • 18
  • 1
    This is just a one to many relationship. E.g a user can have many phone numbers. This will be covered in a database concepts tutorial somewhere. – Arran Sep 13 '12 at 08:48

2 Answers2

36

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`
Björn
  • 29,019
  • 9
  • 65
  • 81
  • 2
    Regarding the 2nd table, is the phone_numbers.id field necessary? Would it be ok to do `update phone_numbers where user_id = '12345' AND phone_number = '123-456-7890'` I want to do this correctly, but I'm trying to understand why the phone_numbers.id field is needed. Thank you! – cmpreshn Aug 14 '14 at 04:00
  • @cmpreshn - I wouldn't say it's necessary, depending on your project of course. – Björn Aug 14 '14 at 05:08
0

I think you need to create a one to many relationship table.

You can see more infos here: http://dev.mysql.com/doc/workbench/en/wb-relationship-tools.html

Fábio Nicolau de Lima
  • 1,484
  • 15
  • 12