This post is for fans of Monster Hunter and Database stuff.
If you study database systems, you can use this post as an application example.
You can find on the web a lot of applications that permits you to search charms of Monster Hunter Portable 3rd.
A couple of famous examples with:
- Omamori Navi (JP)
- Hore Mokoaki (JP)
- Otacoo.com Charms searcher (JP & EN), a little bit of private promotion never hurts :p
The point is: How the hell did they built that?
This image shows the different table of the database.
(＊) means the primary keys of each table. Multiple (＊) means that the primary keys is built on multiple column data.
Arrows indicate the dependencies between each table. It is called a FOREIGN KEY.
The most important part is the detailed table. This one contains all the data for each Charms (Skills, points, charm name, stone name, table number). And as it is the one using the most data, it has been reduced a maximum to use only numerical IDs.
Creating those tables can be made with the following SQL for stone table:
CREATE TABLE stone_table (
CONSTRAINT stone_list_key PRIMARY KEY(stone_id)); -- stone_id is now unique!!
Charms and Skill table have the same shape, so I think you can imagine how their SQL looks .
For the detailed table, something like that:
CREATE TABLE detailed_table (
CONSTRAINT detailed_key PRIMARY KEY(charm_id, seed_num), -- unique ID for each Charm
FOREIGN KEY (charm_id) references charms_table(charm_id), -- refers to charm table
FOREIGN KEY (stone_id) references stone_table(stone_id), -- refers to stone table
FOREIGN KEY (skill_1_id) references skill_table(skill_id), -- refers to skill table
FOREIGN KEY (skill_2_id) references skill_table(skill_id)); -- refers to skill table
A little bit of technical background…
- PRIMARY KEY is a table constraint that forces the tuples (the lines) of the tables to have a unique value of it, this permits to avoid duplicate values in the table.
- FOREIGN KEY a table constraint that permits to refer to another table.
Why using such a structure database structure?
For Charms searcher, the table containing the most data is the detailed table, so you need a light way to manage it.
For instance, imagine you want to look for Charms having the Stone name “Soldier Stone”.
You have to look in the stone table where is the line of the soldier Stone, and take its ID.
WHERE en_name LIKE 'Soldier Stone';
With this ID, simply look for the data in the detailed table.
WHERE stone_id = ??
?? being the value you found previously in the stone table.
Then the secret is to limit the number of requests to the Database if you deal with a large amount of data.
This can be done in 1 query to the charms Database with JOIN mechanisms.
And don’t worry, this is used in the current version of the Charms Searcher