2

I'm creating a hybrid between "X-Com Enemy Unknown" and "The Sims". I maintain game state in a database--PostgreSQL--but my question is structural, not engine-specific.

  1. As in X-Com, there are some bases in different locations, so I create a table named Base with ID autoincrement identity as primary key.
  2. Every base has some facilities in its territory, so I create a table named Facility with a foreign key Facility.Base_ID, referring to Base.ID.
  3. Every base has some landing crafts in its hangars, so I create a table named Craft with a foreign key Craft.Base_ID, referring to Base.ID.
  4. Every base has some troopers in its barracks, so I create a table named Trooper with a foreign key Trooper.Base_ID, referring to Base.ID. Just to this point, everything seems to be ok, doesn't it? However...
  5. I want to have some sort of staff instruction. Like in the X-Com game, every trooper can be assigned to some craft for offense action, or can be unassigned. In addition, every trooper can be assigned to some facility (or can be unassigned) for defense action. So, I have to add nullable foreign keys Trooper.Craft_ID and Trooper.Facility_ID, referring to Craft.ID and Facility.ID respectively.

That database has a redundancy. If some trooper is assigned to a craft or to a facility (or both), it has two (or even three) relations to the base--one direct relation through its Base_ID and some indirect relations as Facility(Trooper.Facility_ID).Base_ID and Craft(Trooper.Craft_ID).Base_ID. Even if I get rid of Trooper.Base_ID (e.g. I can make both assignment mandatory and create a mock craft and a mock facility in every base), I can't get rid of both trooper-facility-base and trooper-craft-base relations.

In addition to this redundancy, there is a worse problem--in case of a mistake, some trooper can be assigned to a craft from one base and to a facility from another base, that's a really nasty situation. I can prohibit it in the application business logic tier, but it's still allowed by the database.

There can be some constraints to apply, but is there any structural modification to the schema that can get rid of the redundancy and potential inconsistency as a result of a good structure, not as a result of constraints?

CREATE TABLE base (
  id int PRIMARY KEY
);

CREATE TABLE facility (
  id int PRIMARY KEY,
  base_id int REFERENCES base
);

CREATE TABLE craft (
  id int PRIMARY KEY,
  base_id int REFERENCES base
);

CREATE TABLE trooper (
  id int PRIMARY KEY,
  assigned_facility_id int REFERENCES facility,
  assigned_craft_id int REFERENCES craft,
  base_id int REFERENCES base
);

Now I want to get some sort of constraints on a trooper t so that

facilities.get(t.assigned_facility_id).base_id IS NULL OR EQUAL TO t.base_id
crafts.get(t.assigned_craft_id).base_id IS NULL OR EQUAL TO t.base_id

This hypothetical constraint has to be applied to table trooper, because it applies in boundaries of each trooper row separately. Constraints on one table have to check equality between fields of two other tables.

I would like to create a database schema where there is exactly one way, having a trooper.id, to find its referenced base.id. How do I normalise my schema?

13
  • "the database has a redundancy." Oh? What exactly does "redundancy" mean & how is it present here? "has two (or even three) relations to the base" So? What exactly do you mean "relations" & what is the problem? Does this have something to do with the previous "redundancy"? "one direct relation through its Base_ID and some indirect relations" So a "relation" is a FK? (A relational DB "relation" is a table. And a FK is a constraint, though often wrongly called a "relationship"/"relation".) "can't get rid of existence of both" So what? "not as a result of constraints" What does that mean? Commented Nov 17, 2022 at 10:42
  • 1
    Please add sample code to illustrate what you mean. Commented Nov 17, 2022 at 10:43
  • "in case of a mistake, some trooper can be assigned to a craft from one base and to a facility from another base" Put the base associated with a craft or facility in those tables & have FKs for crafts & facilities include the base in a FK where relevant. This is a faq. But one must pin down via a minimal reproducible example & write many clear, concise & precise phrasings of one's question/problem/goal to search reasonably. PS You have (sub)types of craft & of facilities--per their base. How can you represent inheritance in a database? [More] Commented Nov 17, 2022 at 10:45
  • 1
    Please clarify via edits, not comments. PS I just told you how to prevent craft/facilities associated with the wrong base, add base to the FKs. PS As I already told you, since a FK is associated with a subset of values, every FK is associated with a subtype & you have types of craft & of facilities--per what base they are in. Your not having thought of those as being types of things doesn't make it not so & doesn't make subtyping/inheritance idioms inapplicable. You don't explain what you mean by "structural" or "structural level". PS FKs arise from table designs, they don't drive them. Commented Nov 17, 2022 at 23:09
  • 1
    @philipxy See the edits. I think the problem is clear. What is the best way to design a database schema that models these game objects and the constraint that troopers from one base can only be assigned to crafts and facilities of the same base? Commented Nov 19, 2022 at 22:20

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.