How to model complex data

Question

I have to create a model that stores gameplay controls for games on several platforms. I am having trouble finding the right approach.

1. Controller buttons are different on different systems (Wii, Xbox 360, PS3 have their own specific controller buttons.)
2. Some games are actually several games packaged as one; this means a single product can have several controls depending on the game being played.
3. Gameplay controls for many games change depending on your in-game activity (driving/walking/flying, etc...)
4. It's not sufficient to just store button functions since most games have several special moves that require a combination of buttons.

How would you approach this problem? Honestly, I don't know where to start!

Update: Many thanks to everyone for your thoughtful, and very helpful input. I am yet to choose and answer because, in the end, it was decided that this feature is not worth the effort. Many thanks again.

Answer 1

Some controllers share common layouts but with different faces, i.e. 360 and PS3 (X is A, triangle is Y, etc). With extra peripherals like fight sticks, flight sticks, guitars, etc - they're just different faces mapping to the console's expectations as well. Since the buttons are usually defined before any controller is even molded, you can do the same.

Each mapping won't all apply to every controller, so it may not be ideal - considering modern console controllers, it should be fine. If you add in Intellivision controllers and keyboard/mouse, things might start to get weird.

// using CHAR(8) for absolutely no good reason. change at will.
CREATE TABLE button_maps (
id tinyint unsigned not null auto_increment,
map_id char(8) not null,
primary key (id),
unique key map_id (map_id)
);

INSERT INTO button_maps (map_id)
VALUES
// dual analog, any direction
('ANA_LFT'), ('ANA_RT'),
// 4-button compass face
('BT_N'), ('BT_S'), ('BT_E'), ('BT_W'),
// shoulder buttons
('BT_LT1'), ('BT_LT2'), ('BT_RT1'), ('BT_RT2'),
// system buttons
('BT_START'), ('BT_SEL'), ('BT_MENU'),
// analog stick click-in, usually called "L/R 3"
('ANA_L3'), ('ANA_R3'),
// 8-direction d-pad - add clockface points for both analogs too
('DPAD_N'), ('DPAD_S'), ('DPAD_E'), ('DPAD_W'),
('DPAD_NW'), ('DPAD_NE'), ('DPAD_SW'), ('DPAD_SE'),
// and DS stylus so it's not obvious what I'm looking at right now
('STL_TAP'), ('STL_DTAP'), ('STL_DRAG'),
// and so on

Note: I have no clue how those full-body motion control things are handled internally, good luck if you ever have to deal with them. LFOOT_HOKEYPOKEY or something.

Note 2: Seriously, don't use a char(8) there. Get detailed, but keep it general enough to apply to a generic controller style and not brand.

Now the buttons per each brand of controller, with their name (assumes a "controllers" table):

CREATE TABLE buttons (
id tinyint unsigned not null auto_increment,
controller_id tinyint unsigned not null references controllers.id,
map_id tinyint unsigned not null references button_maps.id,
button_name varchar(32) not null,
primary key (id),
unique key controller_map (controller_id, map_id)
);

INSERT INTO buttons (controller_id, map_id, button_name)
VALUES
(2, 1, 'Left Analog'), (2, 2, 'Right Analog'),
(2, 3, 'Y'), (2, 4, 'A'), (2, 5, 'B'), (2, 6, 'X'),
(2, 7, 'Left Trigger (LT)'), (2, 8, 'Right Trigger (RT)'),
(2, 9, 'Left Bumper (LB)'), (2, 10, 'Right Bumper (RB)')
// and so on.  PS3 with button shapes and R1/2, L1/2 instead of trigger/bumper

Now, for the actions a button press (or multiple buttons, or a sequence) represents to a game. This doesn't account for the context (2 & 3 of the original question), e.g. game mode or alternate button configurations, but Josh Smeaton and littlegreen already covered that.

This defines actions per each individual game, which isn't very efficient. You might be able to condense things significantly by adding a generic layer of game "types." Many games within a certain genre/perspective have common controls, and it's only becoming more common (console FPSes adding predefined Halo and CoD-style button configurations since players know them, and such). So, if you can define a set of common actions per each genre and use this only to override/extend those defaults as needed, you might be able to pull off a much cleaner solution.

First, define each action:

CREATE TABLE game_actions (
id int unsigned not null auto_increment,
game_id int unsigned not null references games.id,
action varchar(32) not null,
primary key (id)
);

INSERT INTO game_actions (game_id, action)
VALUES (1, 'Shoot'), (1, 'Reload'), (1, 'Turn Left'), (1, 'Turn Right')
// and so on

And finally, define the button presses associated with each action. The "ordinal" field is for combo sequences, like fighting game combos - single actions are 0th ordinal and sequences count up from 1, just to make them easy to differentiate. It doesn't account for timing, so you might need a "nothing" button as a rest for some of the more complex combo games (Soul Caliber et al).

CREATE TABLE game_action_buttons (
id int unsigned not null auto_increment,
game_action_id int unsigned not null references game_actions.id,
ordinal tinyint unsigned not null,
button_map_id tinyint unsigned not null references button_maps.id,
primary key (id)
);

INSERT INTO game_action_buttons (game_action_id, ordinal, button_map_id)
VALUES
(1, 0, 8), // right trigger to shoot
(2, 0, 6), // west face button (X/square) to reload
(3, 0, 7), (3, 0, 9) // combo: both bumpers for rear view look-back while driving
// and a Street Fighter shoryuken on the d-pad to show a sequence:
(4, 1, 21), // DPAD_E: right
(4, 2, 20), // DPAD_S: down
(4, 3, 26), (4, 3, 4) // DPAD_SE + BT_S: down/right + fierce... i think.

(Disclaimer: I created a similar database for a game studio I worked at. Not quite the same, but similar enough that I'm kind of intentionally leaving a lot out. Sorry! Hopefully that's enough to kickstart some ideas though, and it's a fun problem.)

Answer 2

You can try this for starters (EDIT: second try):

Game
----------
(PK) GameID integer
GameTitle varchar(100)

Controller
----------
(PK) ControllerID integer
ControllerDescription varchar(100)
(FK) GameID integer

Effect
----------
(PK) EffectID integer
EffectDescription varchar(100)
(FK) ControllerID integer

Buttons
----------
(PK) ButtonID integer
ButtonKey varchar(25)
(FK) EffectID integer

For example:

GameID  GameTitle
----------------------------------------------
1       Super Mario Bros.

ControllerID ControllerDescription GameID
----------------------------------------------
1            Main Controller       1

EffectID EffectDescription ControllerID
----------------------------------------------
1        Run               1
2        Jump              1
3        Pause             1
4        Move Left         1
5        Move Right        1

ButtonID ButtonKey      EffectID
----------------------------------------------
1        B              1
2        Direction Pad  1
3        A              2
4        Start          3
5        Left Pad       4
6        Right Pad      5