Good evening. I am creating a database for a game, so to speak, but observing the Entity-Relationship model created for said database, I notice that I have some problems.
We have the entities Players, Countries, Cells and Armies.
The entity Players has the attributes id, name and total_oro.
The entity Casillas has the attributes id, name, type ('sea', 'province', 'province') and oro_generado.
The entity Countries has the attributes id, name.
The entity Armies has the attributes id, name.
Before this, I proposed the following model. The players have Countries, at least 1, so the cardinality would be 1: N to Countries, since a country only belongs to one player, and Casillas, the player can have several boxes and these only belong to one player or none , since they can be sea, so the cardinality would be 1: N towards Casillas.
The countries, on the other hand, own Casillas and these can belong once more to a Country or none, another 1: N, from Countries to Casillas. Then the entities would be like this.
Casillas : id, name, type ('sea', 'province', 'province'), oro_generado, player_id and country_id.
Countries : id, name, player_id.
But I note that this is a bad design, because instead of three binary relations between three entities, you should make a ternary relationship that when you move to the relational model, get the table Players-Cells-Countries or Property (which is more readable), where you obtain the primary keys of each table, as the primary key of that table. In this way, I can see to which player a box corresponds and to which country that box corresponds.
The first problem comes when I need to nullify a country and player in that table, for the boxes that are sea, where the database would prevent me, since they are primary keys.
The other problem arises with the Armies table, also related to Players through 1: N (an army belongs to a player and a player can have N), and that I must relate it to the table Players-Casillas-Countries or Property to know if the position I occupy is from a square of another player (where there will be a combat). In this case, I do not know if it is possible to make another ternary relationship between Armies , Players and Property , arising from another ternary relationship, worth the redundancy .