I worked on the table structure. I found more simple to build 3 new tables than modifying the existing one. I built a table describing the recipe and another one for the components/products, the last one implements limits on the combines, mainly for cultural tradeskills. Below the 3 tables and a few words commenting the fields :
first table
Code:
mysql> show columns from tradeskill_recipe;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| name | varchar(64) | | | | |
| tradeskill | smallint(6) | | | 0 | |
| skillneeded | smallint(6) | | | 0 | |
| trivial | smallint(6) | | | 0 | |
| nofail | tinyint(4) | | | 0 | |
| container_id | smallint(6) | | | -1 | |
| notes | tinytext | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
tradeskil,skillneeded,trivial,notes are same as before.
the 'name' field is only designed for world builders.
i added a nofail flag (0/1)
container_id permits to add quest recipes combinable on particular bags/boxes/chests, that container is destroyed when combining
second table
Code:
mysql> show columns from tradeskill_recipe_entries;
+----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| recipe_id | smallint(6) | | | 0 | |
| item_id | smallint(6) | | | 0 | |
| successcount | tinyint(4) | | | 0 | |
| failcount | tinyint(4) | | | 0 | |
| componentcount | tinyint(4) | | | 1 | |
+----------------+-------------+------+-----+---------+----------------+
recipe_id <=> tradeskill_recipe.id
item_id <=> items.id
successcount -> number returned on success
failcount -> number returned on failure
componentcount -> number needed for the combine
doing that way, we can :
-we won't have to change the table again is VI starts to create recipes with 12 components or more.
- use as many tools as needed that will be returned on failure/success : component=1, successcount=1, failcount=1
- have products returned on success : component=0, successcount=1 (or more), failcount=0
- have products returned on failure : component=0, successcount=0, failcount=1 (or more)
- have components, destroyed when combining : component=1 (or more), successcount=0, failcount=0
For a combine that transforms the original container in a new one :
tradeskill_recipe :
id=auto_increment
nofail=1
container_id = original container
tradeskill=[whatever, since its nofail]
tradeskill_recipe_entries :
item_id=item returned on the combine
successcount=1
failcount=0
componentcount=0
tradeskill_recipe_entries :
components needed for the combine.
third table
Code:
mysql> show columns from tradeskill_recipe_limits;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| recipe_id | smallint(6) | | | 0 | |
| class | smallint(6) | | | 0 | |
| race | smallint(6) | | | 0 | |
| god | smallint(6) | | | 0 | |
+-----------+-------------+------+-----+---------+----------------+
fields speaks by themselves i think.
Adding that table allows to limit combines to any combination of race/class/god possible.
If the tradeskill's table changes, i could write a convertor from actual design to whatever it ll become
