CODEWITHSUNDEEP
mysqldatabasephpmyadmin
HuBrooH
HuBrooH

Reputation: 26

DataBase Model Person with Multiple Languages

I have a little problem that it might be simple to solve but right now I can't figure out how to do it. I've tried a lot of different compositions but I'm a little confused.

How should I design a database for this situation?

(example) I have 2 tables one of the languages and another of people. So people can talk one or more languages.

This is the model that I got but don't know how to solve the problem

image

But the problem is how do I insert into the people table that Richard can speak English and Chinese? should I create a new table?

Upvotes: 1

Views: 243

Answers (1)

Mureinik
Mureinik

Reputation: 311438

The textbook solution would be to have a "junction" n:m table that holds pairs of IDs, where each row represents a person's knowledge in a single language:

CREATE TABLE person_languages (
    language_id NOT NULL,
    person_id NOT NULL,
    PRIMARY KEY pl_pk (language_id, person_id),
    FOREIGN KEY pl_l_fk (language_id) REFERENCES languages(id),
    FOREIGN KEY pl_p_fk (person_id) REFERENCES people(id)
);

In your example, Richard speaks both English and Chinese, so you'd have two rows in this table (2, 1) and (2, 2).

Upvotes: 2

Related Questions