bdgi — 2012-11-14T17:20:29-05:00 — #1
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?
Thank you in advance.
jeffwalden — 2012-11-14T22:32:05-05:00 — #2
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.
r937 — 2012-11-15T03:50:37-05:00 — #3
yes, drop the auto_increment id column, it's useless
bdgi — 2012-11-15T09:13:20-05:00 — #4
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