Database Architecture Help

Hey guys,

I am no database expert. I have worked with databases for a long time, but do not have experience with the best practices, so am asking some advice here hoping for some help :slight_smile: I am re-creating a central database for some of my websites and want to make sure I launch with the best (most flexible) architecture possible. Design is based around games and their features:

The main table is ‘games’ and contains all fields that are ALWAYS COMMON to the games that will be in the system. At the bottom there are 2 unique identifiers. Those will be joined with the appropriate tables to bring in the xxxxxx_name from the associated tables.

the problem I have are some of the other tables:

Example: GAME_CATEGORIES table. This one will have all different types of ‘categories’ that I want to be able to associate with the game. A game can have multiple categories.

It is the same with GAME_FEATURES and GAME_SYMBOLS.

How should I best architect this? Is my current architecture ok? Do I create 3 association tables with game_uid, xxxxxx_uid to associate each?

Thank you in advance.

When I have a one to many relationship such as your GAME_CATEGORIES as it relates to GAMES, I usually use an intermediate table. So GAMES has an ID and each GAMES_CATEGORIES has an ID. I create a third table that sits in the middle:


You might have multiple rows in this table with duplicate GAME_ID values, but each GAME_ID value should have a unique CATEGORY_ID value. So if game 55 was in categories 2, 4 and 6, your table might look like:

1 | 55 | 2
2 | 55 | 4
3 | 55 | 6

There may be a more eloquent way of handling this, but it’s always worked for me and I’ve not had performance issues in the past, assuming indexes are created properly.

yes, drop the auto_increment id column, it’s useless


ok. that is the way I have always done it, just wasn’t sure it was the best way. Since I have 3 tables (Categories, Features, Symbols) that will require association tables, I didn’t want it to be crazy-complex for joins. I had a similar structure (but slightly different) on another system and it was difficult to come up with the multi-joins to make the data return properly :frowning: