1

I'm working on setting up a database to house properties related to a game, for example whether or not it supports multiplayer, what genres it is, release dates, etc.

It seems like creating two additional tables (genres, genres_data for example) for each category type isn't nearly as dynamic as it could be. My initial thought was to set it up one of two ways...

Have a games table with skeletal information, then a properties table to list all the properties, and a third table to contain all the data related to the game, where essentially only the columns related to each property are used:

games
-----------
game_id
... relevant data

properties
-----------
property_id
title
type
category

properties_data
---------------
game_id
property_id
bool
min
max
date
text(max255)
longtext

Or, have the games table the same, and have properties include a column name then use columns in the third table:

properties
--------------
property_id
title
type
category
column_name

properties_data
----------------
game_id
title
description
release_date_au
release_date_jp
genre_rpg
genre_fps
platform_360
platform_ps3
platform_pc
has_leaderboards
has_downloadable_content
... etc

What's the practical approach in this sort of scenario where you have a half dozen or so types of data related to a row, but a large number of categories and supporting properties in each category? It doesn't seem efficient to make a table for each property type or category (to me).

Jane Panda
  • 1,591
  • 4
  • 23
  • 51

2 Answers2

3

This appears to be a typical Supertype-subtype cluster of tables. Except you have not identified it as such. So, identify it formally, and normalise the data.

  • place common columns in the Supertype (parent)
  • place all columns specific to each Subtype in a separate child table
  • so that you end up with no optional columns
  • if you do, then you need another level of sub-table.

If you go for "dynamically" populated tables, you lose all the control of static tables in a db with the busienss rules defined in DDL (not code). In that case, be aware that these loosely defined tables are famous for ending up as a mess with no data integrity. Read this recent answer to get some context.

Link to Related Answer

The point is, if you do that "dynamic" stuff, do it properly.

But I do not believe you need to go that far. With an ordinary Rdb, you can maintain full relational power and flexibility. More tables are the nature of an Rdb, nothing to be scared of. Done properly, you will have full control, and speed. Which takes me back to the first para.

Community
  • 1
  • 1
PerformanceDBA
  • 32,198
  • 10
  • 64
  • 90
  • Appreciate the response; though it seems like a topic I'm going to have to discuss more in depth than this sort of environment is meant for. Thanks for your help! – Jane Panda Nov 30 '10 at 05:47
  • 1
    @Bob. My pleasure. If it is not a straight Question that can be posted on SO, email me: SO Profile→My website→email. – PerformanceDBA Nov 30 '10 at 06:11
1

from your attribute descriptions - it seems like a third option is appropriate.

games
--------
game_id
name
...

release
----------
release_id
game_id
release_date
release_country

genre
---------
genre_id
name

game_genre
-----------
game_id
genre_id
Randy
  • 16,480
  • 1
  • 37
  • 55
  • The issue I'm running into is that there are quite a few properties, but only a few sub types of data. For example the game platforms it's features (leaderboards, downloadable content, etc), ratings, (E, M18, etc, etc). – Jane Panda Nov 29 '10 at 20:10
  • Sorry enter cut me off there - what I mean is that with so many properties and categories, and so few types of data, it seemed easier to use a category to sort them, that way they can be loaded and handled more dynamically then having static tables – Jane Panda Nov 29 '10 at 20:25
  • well, i think i would stick with this type of normalized approach. even though it will be more tables, you will have selective control on each of them as you progress, like by deleting them entirely if needed. Managing at the meta-layer is possible, but itroduces it's own complexities - to me not worth it in this case. my 2 cents.. – Randy Nov 29 '10 at 23:57
  • I get what you mean now. I'm trying to wrap my head around how to do this in a way that lets it grow fluidly, rather than having to recode the interface/server code every time I want to introduce a new element. I get that everyone agrees it's wrong, but I can't help but see the appeal in being able to grab every property about something in one query (filtered when needed), then sort it out from there. – Jane Panda Nov 30 '10 at 05:48