Mysql database design multiple values for multiple values

Question

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

What's your problem with the second solution again? I don't really understand what you are saying about it and it makes a whole lot of sense to do it like that to me. — Jasper
– Jasper, Commented Nov 3, 2012 at 19:49
@Jasper If I add a new ball, I have to create M new entries to that table. Maybe I'm not used to this, but seemed a lot if N and M are big and I want to query the table from a specific person "i" (e.g. why have to go trough all the entries, if I only want the entries where the first element is "i") — Jorge Leitao
– Jorge Leitao, Commented Nov 3, 2012 at 19:52

Jesus H · Accepted Answer · 2012-11-03 19:53:19Z

2

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.

answered Nov 3, 2012 at 19:53

Jesus H

1,3403 gold badges17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jorge Leitao Over a year ago

There could be a subtlety that I was not seeing.

Jesus H Over a year ago

If you add a new ball, you don't have to create M new entries to that table if most persons have 0 balls. You can insert a new record when you update the amount of balls for a person, and assume 0 balls for a person 'x' if there is no record in the intermediary table.

Collectives™ on Stack Overflow

Mysql database design multiple values for multiple values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related