CODEWITHSUNDEEP
sqldatabase-design
Christian Macht
Christian Macht

Reputation: 466

SQL normalization, many to many on 3 fixed values?

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!

Upvotes: 3

Views: 1199

Answers (2)

Ethan
Ethan

Reputation: 2784

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

Upvotes: 3

Mathew Thompson
Mathew Thompson

Reputation: 56429

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.

Upvotes: 1

Related Questions