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.)
.