Work with Related Tables
This example shows a very powerful technique that allows you to relate one table to another without having to resort to JOIN statements. The technique will sound familiar to anyone who has used entities before, as it relies on the simple concept of entity-valued properties.
Most relational databases contain foreign keys. When some of the columns of a table are tagged as foreign keys, they reference the primary key of another table, thus creating a connection between two tables. For example, a table of orders might have foreign keys to the customers and product table, instead of replicating information about those directly. Take a typical database with a rich structure.
The structure of tables related through foreign keys can be visualized as a graph.
When constructing the EntityStore corresponding to the RelationalDatabase, some special properties are constructed from the foreign keys.
Look at the entity type orders: The foreign key "customerNumber" points to the "customers" table. The framework automatically recognizes that and creates a new property called "customers" (underlined in the summary boxes) that will return Entity objects. In this way, for each "orders" entity there is a corresponding "customers" entity. Register the EntityStore to verify this.
Now you can call EntityValue on an entity of the "orders" type.
The "orders" table is the target of a foreign key from the "orderdetails" tables; this has allowed the framework to create an inverse mapping from the "orderdetails" table, which results in the type "orders" having a property called "orderdetails" that takes EntityClass values.
The big advantage of having these symbolic wrappers around foreign keys is that one can chain property extraction in EntityFunction. For example, start from a customer entity and check who their sales representative is and who the manager of that employee is.
This seems very natural to anyone who is used to the Wolfram Language, but it is very complicated without using these special properties. What one would normally do is to think of this in terms of JOIN statements.
Another big advantage of using related tables is that one can also use them to perform aggregations without thinking in terms of queries. For example, this is the total value of an order.
The equivalent query in terms of aggregations would have been the following.
