Leveling a random loot generator...

[Legacy_Kato -]

               Hi all,

I must turn a random loot generator into a(still random) leveled one, and since this introduces several subtelties, any insights would be much appreciated.

The system works with one MySQL table having only a few columns:
ref, varchar(16)
cat, smallint   -> category
rarity, smallint   -> 1 is lowest, 2 = twice rarer etc...

So, maybe a weight column could be added, there is none because the weights must be dynamic, based on the npc's(or monster) level, it means they must be re-calculated on each item drop, probably by dividing the sum of rarities by each rarity in the specified category, then applying the percentage bonus wich should shrink all but the lowest category rarities(or dynamic weights) by 1% for each 5 npc levels(with a limit of 250 levels, it means that rarities(or dynamic weights) will get down to 50% of their original values), then using ORDER BY RAND()/weight.
 
I have come close to this result while testing with the query browser, but it seems I must scan the table 2-3 times with subqueries, and the final query is messy and could probably be faster... Or maybe part of the job could be made by nwscript(the calculations at least) in order to simplify the query. The fastest method wins.

Thanks a lot for reading and sharing any infos.

Kato
               
               

               
            

[Legacy_Axe_Murderer]

               You could probably modify it to use a different db table for each level or level range fairly easily.
               
               

               
            

[Legacy_Kato -]

               Indeed, thank you Axe, it's a good idea. '>
               
               

               

                     Modifié par Kato_Yang, 12 octobre 2011 - 04:08 .