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 so store an infinite number of values per user.
I couldn't find how to do this anywhere, Hope you can help me! :)
I am a novice in Relational databases.
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`