SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Nov 2012
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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 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:

    DatabaseDesign.jpg

    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.

  2. #2
    Visible Ninja bronze trophy
    JeffWalden's Avatar
    Join Date
    Sep 2002
    Location
    Los Angeles
    Posts
    1,709
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    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.
    TAKE A WALK OUTSIDE YOUR MIND.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by JeffWalden View Post
    There may be a more eloquent way of handling this...
    yes, drop the auto_increment id column, it's useless

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Nov 2012
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •