# Chapter Four: Modeling decisions _Companies, facilities and commodities: choosing between alternatives_ Schema so far If you are starting here, run these two queries in an empty database. ```typeql #!test[schema] define attribute name, value string; attribute country_name, sub name; attribute province_name, sub name; attribute kabupaten_name, sub name; entity place @abstract; entity country, sub place, owns country_name @key; entity province, sub place, owns province_name @key; entity kabupaten, sub place, owns kabupaten_name @key; ``` ```typeql #!test[schema] define relation containment, relates parent @card(1), relates child @card(1); entity country, plays containment:parent; entity province, plays containment:child @card(0..1), # at most one parent plays containment:parent; entity kabupaten, plays containment:child @card(0..1); # at most one parent ``` ## [](#_harder_columns)Harder columns The next six columns are harder. Here are a few rows: mill mill\_group refinery refinery\_group exporter exporter\_group PUCUK JAYA CHELLAM PLANTATIONS NOT REFINED NOT REFINED SUMBER HIJAU UTAMA ROYAL GOLDEN EAGLE TAPIAN NADENGGAN (JAK LUAY) SINAR MAS SMART TBK (TARJUN REFINERY & KERNEL CRUSHING PLANT) SINAR MAS SINAR MAS AGRO RESOURCES AND TECHNOLOGY SINAR MAS DWIWIRA LESTARI JAYA TRIPUTRA AGRO PERSADA LDC EAST INDONESIA LOUIS DREYFUS LDC EAST INDONESIA LOUIS DREYFUS The same names turn up in different columns: `SINAR MAS` is a mill group, a refinery group and an exporter group, and `LDC EAST INDONESIA` is both a refinery and an exporter. Are a mill and a mill group different kinds of thing? Is an exporter a kind of thing at all? ## [](#_option_1_one_type_per_column)Option 1: one type per column Taking the header literally gives six entity types. ![ER diagram: six unrelated entity types mill, mill_group, refinery, refinery_group, exporter and exporter_group](../../_images/conceptual-modeling-option-1.svg) ```typeql define entity mill; entity mill_group; entity refinery; entity refinery_group; entity exporter; entity exporter_group; ``` This model is easy to load, but it records the same company several times, once per column it appears in, with nothing connecting the copies. It also treats being an exporter as a kind of thing, when it is something a company does. Don’t run it; the rest of the course uses option 2. ## [](#_option_2_companies_and_facilities)Option 2: companies and facilities The dataset’s documentation says that each `*_group` column "groups the \[corresponding\] column into parent companies where applicable". So the data describes only two kinds of thing, linked by ownership: * **Facilities**, which are mills or refineries, each owned by at most one company. * **Companies**, which own facilities and other companies. _Exporter_ and _importer_ are not kinds of company. They are roles a company plays in a trade flow, and the same company can play either, or both, in different flows. In UFO’s terms, a company is a kind, and exporter is a role: anti-rigid, because a company can stop exporting without ceasing to be a company. TypeDB types are rigid, so roles of this kind are modeled as roles in relations, not as subtypes. ![ER diagram: abstract legal_entity generalizes abstract facility and company; facility generalizes mill and refinery; ownership relates a company as owner to a facility as owned](../../_images/conceptual-modeling-option-2.svg) Dashed boxes are abstract types. `legal_entity` generalizes companies and facilities, and `facility` generalizes mills and refineries. The dashed line is added in the next section. ```typeql #!test[schema] define attribute facility_name, sub name; attribute mill_name, sub facility_name; attribute refinery_name, sub facility_name; attribute company_name, sub name; entity legal_entity @abstract; entity facility @abstract, sub legal_entity, plays ownership:owned @card(0..1); # at most one owner entity mill, sub facility, owns mill_name; entity refinery, sub facility, owns refinery_name; entity company, sub legal_entity, owns company_name @key, plays ownership:owner; relation ownership, relates owner @card(1), relates owned @card(1); ``` * A two-level hierarchy: `mill` and `refinery` are kinds of `facility`, and facilities and companies are kinds of `legal_entity`. * `facility plays ownership:owned`: a role played by a supertype is inherited, so mills and refineries can both be owned. * Companies are identified by name. Mills and refineries own a name but have no key: Trase identifies facilities by its own IDs, which this excerpt drops, and facility names are not guaranteed to be unique. Subtype, role or attribute? This choice comes up in most models. Three questions help: 1. **Does it have its own identity and properties?** If not, it is an attribute. `product_type` has two values and nothing else, which makes it a candidate for an attribute (see [Commodities](#commodities)). 2. **Is membership permanent?** If an instance belongs for its whole existence, as a mill is always a mill, a subtype fits. If membership depends on what the instance does, as with exporters, model it as a role in a relation. 3. **Does the distinction add properties or constraints?** A subtype is useful when it owns, plays or relates something its siblings don’t. Otherwise an attribute may do. ## [](#_companies_owning_companies)Companies owning companies In some rows, `LDC EAST INDONESIA` is both the refinery and the exporter, and `LOUIS DREYFUS` is the group of both. We interpret this as: the company LDC East Indonesia owns the refinery of the same name and handles its exports, and Louis Dreyfus is its parent company. So companies, too, can be owned: ```typeql #!test[schema] define entity company, plays ownership:owned @card(0..1); # a company has at most one parent company ``` Ownership now forms a hierarchy of any depth: a parent company owns a company, which owns a refinery. [Chapter Seven](../07-querying/index.md) queries it to any depth with a recursive function. ## [](#commodities)Commodities The data has two commodities: `PALM OIL` and `REFINED PALM OIL`. There are three reasonable ways to model them: Option Model Trade-off a One entity type `commodity`, with a name restricted to the two values Simplest. Adding a commodity means changing the value set. b Two independent entity types, `palm_oil` and `refined_palm_oil` Every role a commodity plays must be declared twice, and queries over all commodities must name both types. c An abstract `commodity` with two subtypes Queries over all commodities stay simple. Useful when the subtypes own or play different things. Option a is enough for now: ```typeql #!test[schema] define attribute commodity_name, sub name, value string @values("PALM OIL", "REFINED PALM OIL"); entity commodity, owns commodity_name @key; ``` `@values` restricts an attribute type to a fixed set of values, like an enumeration. Combined with `@key`, it also means there can be at most two commodities: each needs a distinct name, and only two names exist. ## [](#_challenge)Challenge Implement option c with the same guarantee: at most one instance of each commodity subtype. Answer Put the value restriction on each subtype’s ownership, rather than on the attribute type. Run this only in a separate, empty database with the places schema, since it conflicts with option a: ```typeql define attribute commodity_name, sub name; entity commodity @abstract; entity palm_oil, sub commodity, owns commodity_name @key @values("PALM OIL"); entity refined_palm_oil, sub commodity, owns commodity_name @key @values("REFINED PALM OIL"); ``` Each subtype can now only own its own name, and the key makes that name unique, so each subtype has at most one instance. Next: [Chapter Five: The n-ary relation](../05-n-ary-relations/index.md). [Chapter Three: Entity types and generalization](../03-entity-types-and-generalization/index.md) [Chapter Five: The n-ary relation](../05-n-ary-relations/index.md) [Edit on GitHub](https://github.com/typedb/typedb-docs/edit/3.x-development/learn-typedb/modules/ROOT/pages/conceptual-modeling/04-modeling-decisions.adoc) Edit this page on GitHub.