CODEWITHSUNDEEP
mysqldatabasedatabase-design
Dima Mironov
Dima Mironov

Reputation: 575

Relationship tables concept: check my understanding, please

I'm designing my first big database and want to check, whether I'm OK in providing tables relationships.

I'm designing a webapp where

  1. users can play games in teams
  2. each game has it's categories
  3. users create their team for the game and choose their team category
  4. each game has it's own subset of categories enabled
  5. in fact categories slightly differ from game to game. For example, there are slowpoke categories in game1 and game2, but they differ in alowed time.

How do I hope to store this:

  1. there are tables users, teams, games, categories, each with it's adequate data fields and INT primary_keys
  2. there is user-team M-N relationship table
  3. there is game-category M-N relationship table, with alowed time column
  4. there are category_id and game_id foreign key columns at team table

My common queries:

  1. user -> all his/her teams, in which they were games, categories
  2. game -> all it's categories
  3. game -> all teams paticipated, with category, with users
  4. game -> all users participated
  5. team -> it's game, category

My suggestions to me:

  1. Make a real-category id creating a separate INT primary index in category-game relation table and use it in team table
  2. Create separate table with team_id category_id game_id and set all them as a concatenated primary index
  3. Just set game_id in teams table as INDEX

The question is if this thing is OK and what suggestions to accept?

Upvotes: 1

Views: 127

Answers (1)

Alain Collins
Alain Collins

Reputation: 16362

Some thoughts:

  • Use UNSIGNED INT for primary keys rather than INT.
  • You must have some TEAM -> GAME mapping, right?

Given that, it seems like you can easily process your "common queries".

What problems were your "suggestions" trying to solve?

Upvotes: 2

Related Questions