How to implement disjoint (nonoverlapping) subtype with total completeness in MongoDB?

Question

I designed an entity relationship diagram (ERD) with the goal of creating two child entities (INSTITUTION and SPACECRAFT) of a parent entity (LOCATION), which is distinguished depending on the value of a boolean attribute (IS_SPACECRAFT). Implementing this in MongoDB, I'd like to link the INSTITUTION entity to another separate entity when IS_SPACECRAFT is false (0), and the SPACECRAFT entity to this other separate entity when IS_SPACECRAFT is true (1).

Right now, I am doing this manually by creating two attributes (SPACECRAFT_ID and INSTITUTION_ID) in this unrelated entity and pasting the object ID of either the INSTITUTION or SPACECRAFT entity in this attribute depending on the value of IS_SPACRECRAFT. But this inevitably leads to one of those attributes being NULL, which I think is poor database design and is something I'd like to avoid. With that in mind, is there a way to link another entity (i.e., as a foreign key) depending on a Boolean value in MongoDB?

Attached below is the ERD I mentioned earlier so the hierarchy is clear. The discriminator (d) is meant to represent a disjoint (overlapping) subtype, and the double lines represent a total completeness constraint, such that all the member of the parent LOCATION entity are in either SPACECRAFT or LOCATION, but not both.

enter image description here

For reference, a disjoint subtype is defined as:

Disjoint subtypes, also known as nonoverlapping subtypes, are subtypes that contain a unique subset of the supertype entity set; in other words, each entity instance of the supertype can appear in only one of the subtypes.

https://www.google.com/books/edition/Database_Systems_Design_Implementation_M/4JN4CgAAQBAJ

Answer 1

The parent entity location has one-to-one relation ship with the institution and spacecraft entities. MongoDB allows storing de-normalized form of data. You can structure your data, for example, as follows as a single collection document with location, spacecraft / institution details within the same document.

location:

{
 _id: <ObjectId>,
 location_details: <some location specific details>,
 child_data: {
 type: <string>, // valid values would be "spacecraft", "ïnstitution"
 id: <number>, // spacecraft_id or institution_id, depending upon the type
 data: {
 // specific fields depending upon the type
 }
 }
}

This works, in general, for your use case. You can enforce this structure in your database by applying constraints (or validation) on the collection. You can create the location collection using the Schema Validation feature. Schema Validation allows database server side validation for Insert and Update operations. An example validation that can be applied is that the "child_data.type" is an enum with values "spacecraft" or "institution" only. Also, you can define validation on the other fields based upon the "child_data.type". Note that Schema Validation is a optional feature.

Data modeling requires information related to the use case, the amount of data, the application functionality as in CRUD operations, other requirements, etc., and there factors can influence the data structure of the documents in a collection.

• ttoshiro: Thanks! Just to clarify, the id would be a single id that encompasses both spacecrafts and institutions, correct?
• prasad_: The "child_data.id", value would be based upon the "child_data.type"; you are correct.