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.
- As in X-Com, there are some bases in different locations, so I create a table named
Base
withID
autoincrement identity as primary key. - Every base has some facilities in its territory, so I create a table named
Facility
with a foreign keyFacility.Base_ID
, referring toBase.ID
. - Every base has some landing crafts in its hangars, so I create a table named
Craft
with a foreign keyCraft.Base_ID
, referring toBase.ID
. - Every base has some troopers in its barracks, so I create a table named
Trooper
with a foreign keyTrooper.Base_ID
, referring toBase.ID
. Just to this point, everything seems to be ok, doesn't it? However... - 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
andTrooper.Facility_ID
, referring toCraft.ID
andFacility.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?