samedi 13 février 2016

Proper database schema with composite key

I'm writing a Python program that takes a list of game objects, simulates playing a game, and then records various statistics on that game when using those objects, generating millions of games worth of data within seconds.

I decided that the way I would like to record my results is in database form and I have a few tables I need. Relevant to the question is a table containing the unique combination of game objects used, and the table containing statistics on games run with those objects in a 1 to many relationship. However, I'm running into a problem with my game object table. Lets say I have 3 objects, A, B, and C. I would like my primary key to be descriptive of what it contains, so I thought I would use a composite key on the object fields, as each combination of fields A, B, and C should be unique.

This results in a lot of data duplication in my game stat table though because the foreign key has to contain all of these game object fields which make up a huge percentage of the game object table.

My next thought, was that because my objects A, B, and C are all numbers that I could condense them into a single primary key value. If A = 5, B = 6, and C = 9 in a particular configuration then my primary key would be 549. This however won't work because I occasionally need values of 10 or greater and the fields A=5, B=10, C=9 making 5109 could be read as A=51, B=0, C=9 just as easily as it could be read as the correct value. So then I thought I could add 0's inbetween each field as spacers and parse them back out to the correct values in python, so the previous example would read 0501090, but this doesn't work either because it still can't tell the difference between 1 and 10, it would have to be 00 (my max size on a game object goes to 60, so x00 is never a valid number) making it 5001000900. This could work for a small number of game objects but I have around 16 different objects to track (and the potential for more in the future). The spacing zero's quickly make my key larger than the maximum 8 byte integer size.

So now I'm stuck. What is the proper way to go about this?

Aucun commentaire:

Enregistrer un commentaire