My inclination there would be to create a single table with resref, weight, and category columns, at a minimum. I would handle category selection in nwscript, as well as the chance of getting nothing.
Here's a sample of our RandomItemProperty Properties table.
mysql> select * from rip_props limit 5;
+-------+---------+--------+---------+---------+----------+--------+--------+------------+-----------+---------------+-------------+-----------+----------------+
| rp_id | rp_type | rp_sub | rp_cval | rp_pval | rp_range | rp_min | rp_max | rp_inverse | rp_rarity | rp_allow_item | rp_block_ip | rp_weight | rp_std_dev_cap |
+-------+---------+--------+---------+---------+----------+--------+--------+------------+-----------+---------------+-------------+-----------+----------------+
| 1 | 0 | 0 | -1 | 0 | (x*2)+4 | 1 | 16 | 0 | 1 | 1073872895 | x | 10150 | 2 |
| 2 | 0 | 1 | -1 | 0 | (x*2)+4 | 1 | 16 | 0 | 1 | 1073872895 | x | 10150 | 2 |
| 3 | 0 | 2 | -1 | 0 | (x*2)+4 | 1 | 16 | 0 | 1 | 1073872895 | x | 10150 | 2 |
| 4 | 0 | 3 | -1 | 0 | (x*2)+4 | 1 | 16 | 0 | 1 | 1073872895 | x | 10150 | 2 |
| 5 | 0 | 4 | -1 | 0 | (x*2)+4 | 1 | 16 | 0 | 1 | 1073872895 | x | 10150 | 2 |
+-------+---------+--------+---------+---------+----------+--------+--------+------------+-----------+---------------+-------------+-----------+----------------+
5 rows in set (0.00 sec)
I would still include an auto-incrementing column, which is what rp_id is. Past that, I suspect you would want resref, category, and weight. You might also want a meta-weight column of some sort, depending, but you could easily handle that in nwscript.
What do I mean by meta-weight? Something that treats the weight of an item as the relative rarity, and then uses it to assign a relative weighting number based on the total number of items, or the total number of items in that items category, depending on your intended use. It would operate something like this:
void SetAugmentWeights (object oPC) {
SQLExecDirect("SELECT FLOOR(ra_rarity / 1000) AS a, COUNT(DISTINCT ra_rarity % 1000) AS b " +
"FROM rip_augments GROUP BY a HAVING b <> 1");
if (SQLFetch() != SQL_ERROR) {
do {
WriteTimestampedLogEntry("INVALID RARITY SUBclass : " + SQLGetData(1) + " : " + SQLGetData(2));
} while (SQLFetch() != SQL_ERROR);
return;
}
SQLExecDirect("SELECT a.ra_rarity, COUNT(*), " +
" (SELECT COUNT(DISTINCT b.ra_rarity) FROM rip_augments AS b WHERE " +
" b.ra_rarity % 1000 = 0 AND " +
" FLOOR(b.ra_rarity / 1000000) = FLOOR(a.ra_rarity / 1000000)) AS share " +
"FROM rip_augments AS a GROUP BY a.ra_rarity ORDER BY a.ra_rarity DESC");
int nLastRarity = -1, nRemaining = 0;
string sSQL = "";
while (SQLFetch() != SQL_ERROR) {
int nRandP = StringToInt(SQLGetData(1));
int nCount = StringToInt(SQLGetData(2));
int nProportion = nRandP % 1000;
int nRarity = nRandP / 1000000;
if (nRarity != nLastRarity) {
nLastRarity = nRarity;
nRemaining = 1000;
}
int nWeight = GetRandomIPRarityclassWeight(nRarity);
if (nProportion != 0) {
nWeight = (nWeight * nProportion) / 1000;
nRemaining -= nProportion;
} else {
int nShare = StringToInt(SQLGetData(3));
nWeight = (nWeight * nRemaining) / (nShare * 1000);
}
sSQL += " WHEN " + IntToString(nRandP) + " THEN " + IntToString(nWeight / nCount);
}
if (sSQL != "")
SQLExecDirect("UPDATE rip_augments SET ra_weight = (CASE ra_rarity " + sSQL + " ELSE 0 END)");
}
It's a little more complex than if you don't use wieghting at all. Under such a system, you would then pick an random (weighted) item (or augment, in this case), like so:
struct Augment GetRandomAugment(string sConditions) {
string sSQL;
struct Augment ret;
//pull up all augments that are of acceptable IP and item type, totalling weight
sSQL = "SELECT @ra_random := FLOOR(RAND() * SUM(ra_weight)) " +
"FROM rip_augments WHERE " + sConditions;
SQLExecDirect(sSQL);
//select one of those augments by weight
sSQL = "SELECT ra_id,ra_name,ra_block_aug,ra_allow_item,ra_notes,ra_affix,ra_appearance,ra_rarity FROM rip_augments WHERE (" + sConditions +
") AND (@ra_random := @ra_random - ra_weight) < 0 LIMIT 1";
SQLExecDirect(sSQL);
while (SQLFetch() != SQL_ERROR) {
ret.id = StringToInt(SQLGetData(1));
ret.name = SQLDecodeSpecialChars(SQLGetData(2));
ret.blockaug = SQLGetData(3);
ret.allowitem = StringToInt(SQLGetData(4));
ret.notes = SQLDecodeSpecialChars(SQLGetData(5));
ret.affix = StringToInt(SQLGetData(6));
ret.appear = StringToInt(SQLGetData(7));
ret.rarity = StringToInt(SQLGetData(
);
}
return ret;
}
Whether or not you want to deal with weighting at all is going to depend on how much you care about fine-tuning item drops. If you don't want to, there may be very little reason for you to go database-driven at all. MySQL is fantastic for dynamically recalculating weighting when you add items, but if you want them all to have roughly the same drops, it may not matter all that much. Or, you can achieve rough weighting using varying levels of brackets in case or if statements. Something like this:
Funky
