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 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?
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:
ID | GAME_ID | CATEGORY_ID
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.
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
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: