game,cpnumplayers,p1joy2,p1joy4,p1joy8,p1spinner,p1button1.p1button2,... ...p2joy2,p2joy4
NO NO NO. Bad design! Hehe.
Need it more generic, it will make life easier (especially if you want this to work with other emus to.
The DB would have many tables.
One table would store the game name, game index, otherstuff. The important part is game index.
Let's say it has these records:
game index|game name
232|Robotron
362|Mr .Do!
467|Discs of Tron
483|Street Fighter
Then you would have a controls table. The records that show the controls for the above game would be like:
game index|mame control|cp control|label
232|2joy8way|joy1|
232|2joy8way|joy2|
362|joy4way|joy4way|
362|1button|P1B1|Fire
467|dial|up/down spinner|
467|joy8way|tronjoy|
467|button1|tronjoybtn1|Fire
467|button2|tronjoybtn2|Shield
483|joy8way|joy1|
483|joy8way|joy2|
483|button1|p1b1|Weak Punch
483|button2|p1b2|Punch
483|button3|p1b3|Hard Punch
483|button4|p1b4|Weak Kick
483|button5|p1b5|Kick
483|button6|p1b6|Hard Kick
483|button1|p2b1|Weak Punch
483|button2|p2b2|Punch
483|button3|p2b3|Hard Punch
483|button4|p2b4|Weak Kick
483|button5|p2b5|Kick
483|button6|p2b6|Hard Kick
Now, this is assuming you have a control panel with a seperate 4way joystick, an up/down spinner, and a tron style joystick.
The 'cp control' field is the label you give the control in the FE, that will get highlighted somehow when that game is selected. When you select a game in the FE, it will get the game index from the games table and retrieve all the records for that index in the controls table (easily done in one SQL command). Then go through each of those records highlighting the control and putting in the text for the label of that control. It can actually be less complicated, but you get the idea. The controls should go in a seperate table and be looked up for ease of use, and ANY control can go in there. With one huge record like you suggested you may not get all controls or future controls that pop up.
Actually, thinking about it, the above example is way more complicated than it needs to be.
The other way I could think up is have a lookup table that lists all the mame controls and the FE label for the control (the represents a control on your cp).
Like:
mame control|cp control
player1button1|p1b1
joy8way|p1joy8way
joy8way|p2joy8way
dial|Spinner
paddle|Spinner
trackball|trackball
Then in the FE the FE would look up the controls in the -listinfo and then correspond them to the above table looking up which FE control to highlight.
Now, if you make a cp designer for your FE when you add a control to the cp a drop down box would appear with all the possibilities for mame controls. Then a text box for a cp control label (like p2joy8way). That gets added to the above table.
Now, you have to correlate say player 1 button 1 to Fire or some other text label. That's a DB like my first example will come in handy. You list all the games and give it an index, then you have another table with all the controls for that game (the mame controls that you get from listinfo) and any label for that control (like button1 is jump)