I have some data that is configuration data for the games I will be including in thesystem.
Each game will have a set of up to a possible 12 mappings per game configuration.
Each mapping is identified by a number 1 to 12 and maps to a string.
so a table like this would describe a mapping if it were in its own table:
Create mapping1(
id int AUTO_INCREMENT PRIMARY KEY,
action varchar (100) NOT NULL
);

Each game will use between 1 and all 12 mappings depending on its requirements and these will not always be the same for each value.
So Mapping 1 could be Load(fred) for game1 and Load(fred(3)) for game2 and game 1 might use all 12 while game 2 might only use mapping 3 and mapping 7.
Can anyone recommend a sane and normalised way to store this data?

I considdered a table for each of the mappings (as above)and then a game config table like this:
Create gameconfig(
id int AUTO_INCREMENT PRIMARY KEY,
gameid int NOT NULL REFERENCES game (id),
mapping1id int REFERENCES mapping1 (id),
mapping2id int REFERENCES mapping2 (id),
mapping3id int REFERENCES mapping3 (id),
mapping4id int REFERENCES mapping4 (id),
mapping5id int REFERENCES mapping5 (id),
mapping6id int REFERENCES mapping6 (id),
mapping7id int REFERENCES mapping7 (id),
mapping8id int REFERENCES mapping8 (id),
mapping9id int REFERENCES mapping9 (id),
mapping10id int REFERENCES mapping10 (id),
mapping11id int REFERENCES mapping11 (id),
mapping12id int REFERENCES mapping12 (id),
);

Now this seems like a waste of table space if some games are not going to use all the mappings, some may only use two.

Any input will be greatly appreciated.

Thanks in advance
Chris.

Hi titchy...
I'd use a table for all of the mappings. So..

You will have the following tables:
gameconfig
mapping

Inside gameconfig you would have mappingID, and gameID.. and all of you other stuff.

Then in the mapping table you would have...
gameID
mappingID
action (whatever your mapping controls are)

And then to get your mapping you do something like ..

SELECT mapping.mappingID from mapping WHERE mapping.mappingID = gameconfig.gameID;

Make sense?
Cheers, good luck
Sir P

Thanks for the reply, I follow what you are saying but what I need is a way for a single mapping to be valid for many games, while there are also many possible mappings for each mapping value 1 ... 12
The information relating to any mapping are:
What mapping number is it 1 ... 12 (there can be many mappings called 1 etc.)
What games does this mapping work with (There are many games that can use each mapping)
What does this mapping do (there are many actions a specific mapping number could perform depending on the game using it)

Or from the game config perspective:
if the game uses a 1 and a 7 mapping which 1 and 7 mappings are the correct ones.

An example of the situation is this:
Games 1 to 4 all use the same mapping for position 2
Games 1 and 4 use the same mapping for position 7
Games 1 to 4 all use different mappings for position 5
Game 1 has an alternate set of mappings, only 2 to 8, which it is not using now but if I wanted to could be used instead of its current set. (will be configurable by database update)
Games 2 and 3 each have a full set of 12 mappings
Game 4 only has mappings 2,7 and 5

Hope that helps with the understanding of what I am trying to do, I just cant seem to find a structure I am confident is normalised and good design.


Thanks again for your input.

Chris.

yo..

What games does this mapping work with (There are many games that can use each mapping)

Thats the important bit I missed... instead of putting a coloum called gameID in the mappings table, put a coloum in there called games.. and have it a comma seperated value..

For example mapping table looks like.

mappingID
games
mappingAction1
mappingAction2
mappingAction3
mappingAction4

and an example result from tat would look like:

mappingID = '1'
games = '3,12,56,7'
mappingAction1 = 'whatever'
mappingAction2 = 'whatever'
mappingAction3 = 'whatever'
mappingAction4 = 'whatever'

and then to find out which mapping works for a certain game just do a SQL that uses LIKE..

..WHERE games LIKE '%,X,%';

(where X is the game ID number)
The reason we are using commas to seperate, is incase you want to do more with it later you can split them in php easy etc etc.

let me know how it goes.
ta

I'd recommend a table for the game, a second for the maps and a third table with MapID and GameID. This way you only need to search this very quick and simple table to find the games OR maps you need.

This won't get point for aethetics if you spend a lto of time micromanaging the db but it would be relatively quick and it would score high points in terms of keeping you sane