SQL normalization, many to many on 3 fixed values?

Question

I'm trying to understand many-to-many relationships, but my head keeps breaking on this hobby project of mine:

In a database of games, I currently have a single table that holds all columns and values.

GAMES
id   name   alt_name1   alt_name2   type                length    group_size
----------------------------------------------------------------------------
1    foo    foozoo      NULL        ice, team, silly    s, m      s, l, xl

However, when implementing search, I came across problems when looking specifically for a silly game with medium length for small groups.

As far as I understood, in order to normalize I would create a new table for name (one id to up to three names). type, length and group_sizeare many-to-many relationships, so I would need to create tables for their values and a table to hold their relation to the games:

type
games_type
length
games_length
group_size
games_group_size

Is there a better way than creating all of these tables?

Thanks for your thoughts!

The table to hold their relation to the games is often called a *junction* table. And yes, this is the correct way to implement many-to-many. Don't let the fact that this table will have many, many rows fool you: SQL databases can handle millions of rows just fine. Also read: http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — NullUserException, Jan 23 '13 at 18:21
Thanks for the assurance! I'm more worried that I'm creating 2 tables to handle only 3 values `s`,`l`,`xl` (in case of `group_size`). Wasn't sure if "externalising" the relations in junction tables was worth it. — Christian Macht, Jan 23 '13 at 18:26

Ethan · Accepted Answer · 2013-01-23T18:26:07.240

3

Any time you have a many to many relationship, you should have 2 tables that contain the different values, then another table that associates the two (junction table).

In a table that contains non-associative records, all other columns should describe properties unique to the primary key. If a descriptive value can appear more than one time in a table, it should probably have its own table (lookup table).

For example:

Items_A
id,name

Items_B
id,name

A_with_B
item_a_id,item_b_id

A common usage of this is users and groups which would look like this:

Users
id,name

Groups
id,name

Group_Users
user_id,group_id

If you extend that example to include a lookup table you could have something like this (where a user can only be in one location, but can belong to multiple groups):

Users
id,name,location_id

Locations
id,name

Groups
id,name

Group_Users
user_id,group_id

edited Jan 23 '13 at 18:26

answered Jan 23 '13 at 18:20

Ethan

2,754
1
21
34

Thanks for the examples. As to my questions whether there is a better way, your answer is no? – Christian Macht Jan 23 '13 at 18:31
@ChristianMacht I believe what you are trying to do is correct. I provided the examples for reference so that you can understand the reasoning. – Ethan Jan 23 '13 at 18:33
2

@ChristianMacht creating all these tables is the better way. I can understand that you don't see it yet, but if you tried a different approach you would get tangled up in what you were trying to do. – Marlin Pierce Jan 23 '13 at 19:40
@MarlinPierce Many thanks, I'll go with it for now and see where it takes me.. ♪♫♪♪ – Christian Macht Jan 23 '13 at 19:58

score 1 · Answer 2 · answered Jan 23 '13 at 18:20

1

That is correct, you are effectively decomposing the many-to-many relationships using the games_type, games_length and games_group_size tables, thus allowing a game to have more than one type, length and group size.

answered Jan 23 '13 at 18:20

Mathew Thompson

55,877
15
127
148

SQL normalization, many to many on 3 fixed values?

2 Answers2