In terms of performance and scalability which one would be a better method to do one to many mappings in MySQL. Using a separate column but sticking to 2 tables: (person) : id, name (phone) : id, number, type, person_id Using a separate table: (person) : id, name (phone) : id, number, type (person_phone) : id, person_id, phone_id

Reputation: 889

Should there be a separate table when mapping one to many relationship?

In terms of performance and scalability which one would be a better method to do one to many mappings in MySQL.

Using a separate column but sticking to 2 tables:

(person) : id, name

(phone) : id, number, type, person_id
Using a separate table:

(person) : id, name

(phone) : id, number, type

(person_phone) : id, person_id, phone_id

Upvotes: 8

Reputation: 11108

First one is better.

P.S. Maybe you even don't need id for phone, (number, type, person_id) is enough.

Upvotes: 5

Reputation: 10148

In terms of performance it is always cheaper to avoid joins, which add multiplicity to the amount of rows to be queried.

As long as a single phone will only be used by one employee (a true one-to-many) the first option is best.

Upvotes: 5

Reputation: 52372

There's only one correct answer to this, and it's the first one.

The second of your ideas is how you model many-to-many relationships, not one-to-many.

Upvotes: 14