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?

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
  • "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? – philipxy Nov 17 '22 at 10:42
  • 1
    Please add sample code to illustrate what you mean. – Laurenz Albe Nov 17 '22 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 [mre] & 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?](https://stackoverflow.com/q/3579079/3404097) [More] – philipxy Nov 17 '22 at 10:45
  • What is your 1 specific researched non-duplicate question re how/why you are 1st stuck on what step among which steps following what published presentation of what design method/process given what? Right now you are essentially asking us to (re)write a textbook with bespoke tutorial with no details on what you misunderstand or do or don't understand. [ask] [Help] Basic questions are faqs. [research effort](https://meta.stackoverflow.com/q/261592/3404097) – philipxy Nov 17 '22 at 10:46
  • What does your mention in the title have to do with the post & why do you think what's in your post is a DB normalization issue? Rearranging tables because of subtyping is not DB normalization. Combining subtypes into a supertype table with 'inapplicable' nulls uses natural outer join, combining normalization-produced component projections back to originals uses natural inner join. – philipxy Nov 17 '22 at 10:55
  • 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. – philipxy Nov 17 '22 at 23:09
  • Again: Please edit to ONE (specific researched non-duplicate) question. PS Please don't insert "EDIT"s/"UPDATE"s, just make your post the best presentation as of edit time. Please avoid social & meta commentary. PS It is not helpful to merely say that you searched. What is relevant is how exactly you searched & specific relevant results. PS Putting words in scare quotes does not clarify the idiosyncratic meaning that you don't make clear by actually saying what you mean. PS Please delete & flag obsolete comments. PS It is not clear what you are trying to say by `EQUALS OR NULL`. – philipxy Nov 18 '22 at 08:13
  • Giving pretend ("hypothetical") code does not communicate to us what you wish it meant, because it doesn't mean anything. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. – philipxy Nov 18 '22 at 08:19
  • @philipxy This doesn't sound like inheritance/subtyping at all. Adding the base id to the FK constraints sounds like the solution, why don't you write an answer for that? – Bergi Nov 19 '22 at 17:52
  • @Bergi This post is not fit for answering or closing as a duplicate. See my comments. – philipxy Nov 19 '22 at 20:59
  • 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? – Bergi Nov 19 '22 at 22:20
  • @Bergi exactly. Thank you so much. Craft and facility, referenced by the same trooper, must reference the same base. So, the constraint must concern the trooper row, but apply to fields of two other referenced rows from two other tables that are not the fields referenced by the trooper table itself. P.S. I'm sure it can easily be achieved by using triggers, but if there is a "table and references" solution, it's strictly preferred, 'cause it's not good to have more than one possibly different expressions to get the same "real" data (in C#-style, trooper.facility.base and trooper.craft.base). – Anthony L. Gershman Nov 21 '22 at 07:01
  • @Bergi Besides using names consistently instead of a, b, c there is no improvement. Moreover you have added more confusion, "How do I normalise". – philipxy Nov 22 '22 at 10:06

0 Answers0