Schema design: creating joins between the tables

Joins create a relationship between the tables so that the correct data is returned for queries that are run on multiple tables and ensure that the data is combined in a meaningful way. If no joins are specified in a database structure, the resulting query produces a Cartesian product, which outputs a report that joins every column in the first table to every column in the second table.

There are several approaches to defining joins in Universe Designer:

  • Manually in the schema, graphically by using the mouse to drag a line between columns in separate tables.
  • In the Edit Join dialog box - select Insert -> Join from the menu or click the Insert join button.
  • Using the Join SQL Editor to explicitly type in the join expression.
  • Joins can also be detected and created automatically. Be careful using this method as it might produce errors because of the fact that the algorithm uses column names as the key for creating joins.

Join properties

Each join has the following properties to define:

  • Table1 - table at the left of the join.
  • Table2 - table at the right of the join.
  • Operator - defines how the tables are joined. The available operators are: =, !=, >, >=, <, <=, Between (theta join) or Complex
  • Outer Join - determines which table contains unmatched data in an outer join relationship.
  • Cardinality - defines the cardinality for the join.
  • Shortcut Join
  • Expression - an editable WHERE clause used to filter the data that is returned when the two joined tables are included in a query.

Join cardinality

The join cardinality defines how many rows in one table will match those in the other. Universe Designer uses cardinality to detect and resolve loops.
The cardinality can be:
- One-to-one (1-1)
- One-to-many (1-N)
- Many-to-one (N-1)
- Many-to-many (N-N)

Join cardinality can be set manually or with the automatic detection tool. For efficiency (detection tool runs 3 queries on every join, which can take a long time) and accuracy (works ok only with totally normalized data) it is strongly advised to apply cardinality manually.