How to store a one to many relation in my sql database ? (MySQL)
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`
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