I'm going to start by telling you the orthodox way to do this. So I'll start describing what you should consider and do. And I want to point this early because then I'm going to tell you to throw that away. What I want you to keep are the intuitions.
You need a reference between two tables, let us say heroes and weapons. Spoilers: either way can work.
You need to consider the multiplicity of the reference. In this case the hero has at most one weapon. And the weapon is held by at most one hero.
We need to consider the navigability. Which are you going to query?
- Given heroes, find out what weapon they have.
- Given weapons, find out what heroes have them.
- Both.
I'm not surprised if the answer is both. Which suggest you could make the reference either way.
By the way, the constraints aren't much different:
If we are going to store the reference to the weapons on the heroes table, then you need:
- A primary key in the weapon table. To uniquely identify weapons.
- A foreign key going from the heroes table to the weapon table. To ensure that the weapon a hero has exists.
- And a unique key on the weapon reference. So you can make sure that only one hero has the weapon.
If we are going to store the reference to the hero on the weapon table, then you need:
- A primary key in the hero table. To uniquely identify heroes.
- A foreign key going from the weapon table to the heroes table. To ensure that the hero that has the weapon exists.
- And a unique key on the hero reference. So you can make sure that the hero has only one weapon.
Now, if we have the freedom to make the references which ever way we prefer, what should we prefer? The answer is that you should prefer a tree or forest structure. That is, you want root tables.
Note: Cycles are allowed. However, special consideration should be put on any cycle. The problem with cycles is that they tend to introduce multiple sources of truth, which you might need to reconcile with constraints. And the constraints may result in a structure that is not easy to update.
My intuition is that heroes are more important than weapons, so the references should flow in that direction. However, this depends on the system (the game) you are doing. You might as well have a game in which heroes are a resource, for example. You have no reason to trust my intuition (after all I don't know your game), so how do you know you have a good structure? A tell-tale sign is that it has clear roots and leaves of references. Another is that making joins is easier. You should be able to tell in what direction the references go, without loops (any remaining cycle will have single root and leaf). And my guess is that the weapon probably does not reference much of anything, so it could be a leaf. While the hero references other things, so it might be a root.
Example: we have teachers that teach some topics, and student that study some topics. We want to make sure the student is studying under a teacher that teaches the topic the student study. Furthermore these are many-many relationships. A student can study multiple topics. A topic can be studied by multiple students. A teacher can teach multiple topics. And topic can be taught by multiple teachers. How do you model that database?
Clearly you have students, teachers and topics. Those are nouns. So make tables for those. For many-many relationships, we add auxiliary tables. So there should be student-topic table references both a student and a topic, and that tells you what topics the student studies. Similarly there should be a teacher-topic table. And hmm… I need to do something for student-teacher. So you can go student - student-teacher - teacher - teacher-topic - topic - student-topic - student. That is a cycle.
Now, in student-topic I need the reference to the teacher so I can verify with student-teacher via foreign key. And something similar happens with the other tables. As a result it would be impossible to update them individually. Furthermore they all end up having the same fields. This is telling you something: make a single table.
So, there will be tables for student, teacher and topic. And a table that references them three. That table is a root. It must be something important in the system. And naming things in software engineering is hard.
But look, if I add the corrects additional requirement it becomes clear: I want to know in what class room at what time. Where should I add that? Clearly I need a table for the class rooms and a reference from our root table. And I can add the class hours too. So the table represents a group of student that study a topic under a teacher at a class room at a time. It is a class! The class is the root, everything else is a leaf.
So, the common wisdom is that you will have some root tables in your system, and everything else comes from them in a forest structure (or as close to a forest structure as possible - In fact, you want it to be as close to a tree structure as possible).
And, finally to ease the queries, you can create views from joins of the tables.
This is the part where I tell you to throw that away. A database normalized is not the best for performance. For starter adding redundancy can improve performance. So you could store both a reference to the weapon in the hero and a reference to the hero in the weapon. For example.
However, if the argument is performance... You should profile!
Which query are you going to do more often? And how slow is to do that query query if you implement it each way? And what if you can keep data cached on the application side?
Also consider how and how often the information will change. If the changes need to happen often you might want to reduce the number of places the information has to be stored and referenced from. But if it isn't, you can afford to store in multiple places. However, you want to have a single place where you update and have it propagate to other places (e.g. using triggers).
In fact, if the data does not update often (that you say that online games only have a few heroes makes me think this is the case), you might compile the database. For example, you could create, design and test weapons in a tool that export them in a simple file format (e.g. json - this also makes it easier to put them in version control), and then a tool that generates a script to update the production database adding the weapon taking those files as input. And that script might take care of propagating the data or any additional updates that you need. So, if you need to write both what weapon a hero has and what hero has a weapon, you can do that.
Please notice that we could be asking this question about the heroes on a hero shooter or similar game, where there is a relatively short list of heroes for player to choose from. Or we could be asking this question about heroes, referring to the characters of each player, which are at least as many as there are players. That is there is hero (the actual hero instance) and hero (the platonic hero type). And the actual hero would reference the hero type. Which has the weapon? Is it that each hero type has a weapon equipped before a player selects it, and that is what you want to query. Or is it that each hero a player has selected has a weapon (possibly set by the player), and that is what you want to query. Those are different queries.
Similarly: there is weapon and weapon. For example, there could be vendor that sells swords. These swords don't exist as individual objects (if the vendor has inventory, it actually has tuples of quantity and the weapon type) and then when the use buys, the actual weapon is created.
If you need to model that, you would say that the weapon has a weapon type. That is, the weapon has a reference to the weapon type. And you could be browsing all the weapons on the inventory and want to know if the weapon is equipped by somebody. Or you could be browsing all the weapon types available at a vendor and want to know there a hero that has equipped a weapon of the same type. Those are different queries.
As you can imagine it make little sense to store a reference to the hero in the weapon type. Let alone verifying that. You would have to check that the actual weapon equipped by the hero is of a type that references the hero. Hey, a cycle. Avoid.
So, have a hard look at your requirements, extract the nouns that should become tables, and add any other auxiliary table you need. Take care of any cycles. If performance is a problem: profile. If you need to add redundancy for performance, do so. In fact, if you need to merge or split tables, do so. However, keep a single place where you do updates, and automate propagating the change. Tools such as constraints, views, triggers, and script generation can help.
