Michael Paquier

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?
Well, they built an interface application (javascript or php) that interacts with a database whose structure is more or less as in the picture below.

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 (
stone_id integer,
name_jp varchar(30),
name_en varchar(30),
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 (
range_id integer,
fabrication_id integer,
charm_id integer,
stone_id integer,
skill_1_id integer,
skill_point_1 integer,
skill_2_id integer,
skill_point_2 integer,
slots integer,
table_id integer,
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.
SELECT stone_id
FROM stone_table
WHERE en_name LIKE 'Soldier Stone';

With this ID, simply look for the data in the detailed table.
SELECT *
FROM 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