CODEWITHSUNDEEP
mysqldatabase
Jorge Leitao
Jorge Leitao

Reputation: 20123

Mysql database design multiple values for multiple values

Sorry for my ignorance about mysql and databases, but I'm kind of learning it.

So, suppose the following situation:

I have a table with definitions (ball_definitions) of balls of different colors:

id color
1 red
2 blue
3 green
...
N

where id is the primary value.

and I have a second table (persons) with definitions of persons:

id name
1 John
2 Peter
3 Michel
...
M

(where id is the primary key)

Now, I want to relate to each person, the amount of owned balls by that persons, conceptually, something like this:

john: 1 red ball, 3 green ball, 0 blue ball
peter: 3 red ball, 2 green ball, 1 blue ball.
...

In such a way that both the M and N can vary (for portability reasons).

My first tough was to let persons' table to have N columns where each column was referring to each color, but that is not portable (requires triggers of each change on the ball_definitions change, and it seems quite hard to query).

On the other hand, creating a new table with a "ManyToMany" relation:

persons_id ball_definitions_id amount
1 1 1
1 2 3
1 3 0
2 1 3
2 2 2
2 3 1

seemed to me a little effort when either the number of persons or the number of balls change.

Is there any better solution?

Cheers, Jorge

Upvotes: 0

Views: 79

Answers (1)

Jesus H
Jesus H

Reputation: 1280

No, there is no better solution.

When you have two tables that have an N to N (many to many) relationship you should use an intermediary table.

The alternative would be to create a column in the persons table for every type of ball, or create a column in the balls table for every person which is more effort than the solution you are describing. If you need to add more balls or persons, things get very complicated and messy. You described the best solution.

Upvotes: 2

Related Questions