How can I store an m:m realtionship into a database?

Question

How can I store m:m relationship data in a database?

Suppose m knows knows m languages and m language can be known by any number of people.

i.e any number of people can known any number of language and any number of language can be known to different number of people.

score 2 · Answer 1 · answered Jun 29 '11 at 11:34

A many to many relationship is best stored with a junction table.

http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php

In your case this would be called PersonLanguage

It would have Foreign Keys to both PersonId and LanguageId.

Both the Person and the Language tables would have a One to Many relationship with the PersonLanguage table.

Note you can either have a compound primary Key on PersonId and LanguageId or create a separate PersonLanguageId column that contains an auto-incrementing number.

StevieG · Accepted Answer · 2011-06-29T11:42:32.553

1

3 tables..

Person (PersonId)
Language (LanguageId)
Person_Languages (Id,PersonId,LanguageId)

edited Jun 29 '11 at 11:42

answered Jun 29 '11 at 11:34

StevieG

8,639
23
31

Grr - a table called People full of Persons - I'd thump you with my coding standards handbook if I had one to hand! – BonyT Jun 29 '11 at 11:37
@BonyT: Hardly a 'coding standard', more a personal preference(http://stackoverflow.com/questions/338156/table-naming-dilemma-singular-vs-plural-names) ...but I've changed it anyway so you feel better ;) – StevieG Jun 29 '11 at 11:48

score 0 · Answer 3 · answered Jun 29 '11 at 11:37

Usually something like this is done with three tables:

People(id, name, etc.)
Languages(id, name, etc.)
SpokenLanguages(peopleID, languageID)

...where SpokenLanguages is an association table that uses foreign keys to associate each person with the languages he/she speaks.

How can I store an m:m realtionship into a database?

3 Answers3