Docs‎ > ‎Live Logic Tutorial‎ > ‎

Sample Database

The Sample Database is illustrated below.  This database is used extensively in the Logic Tutorial.

Relationship Examples

The Sample Database illustrates classic examples of common relationship patterns.

One to Many

See the relationship between Customer (Parent) and Orders (Child).

One to Many Self Relationship

See the self-relationship from Departments to itself.

Many to Many


Many to Many Self Relationship

The product_billofmaterials table represents that:
  • A "kit" Product may have many "component" Products.  For example, a Wing may consist of Engines and Bolts.

  • A "component" Product may be a component of many kits.  For example, Bolts are used in Wings and Engines.
The terms "kit" and "component" are convenient, but note that a Product such as a Wing can be both a component (of a plane) and a kit.

The sample database contains several products:

Now, let's say that:
  • a Boing 747 consists of a Fuselage, Wing and Bolts 

  • a Wing consists of an Engine and Bolts

That is represented by rows in the product_billofmaterials table, as follows:

We have introduced some terminology here.  While all Products are entered into the same Products table, we differentiate:

  • Kit: a Product with components (e.g, Boing, Wing)

  • Component: a Product that is a component (e.g., Fuselage, Bolt, Wing, Engine... note Wing is both a Kit and a Component, a recursive relationship).

Observe there are multiple relationships from product to product_billofmaterials:

  • inKits: product implemented by foreign key product_name - so...
    • product.inKits returns the product_billofmaterials identifying the kits in which a product is a member. So, bolt.inKits returns ( (Boing, Bolt) and (Wing, Bolt) )
    • product_billofMaterials.product returns the component. So, (Boing, Bolt) returns Bolt. 

  • components: kit implemented by foreign key product_name_kit - so...
    • product.components returns the product_billofmaterials identifying the components in this kit. So, boing.components returns ( (Boing, Bolt) (Boing, Fuselage) (Boing, Wing) )
    • ProductBillofmaterials.kit returns the kit. So, (Boing, Bolt) returns Boing.

So, a Boing price calculates to $10,300:

  • 1 Fuselage @ $1300 = $1,300
  • 100 Bolts @ $10 = $1,000
  • 2 Wings @ $4,000 = $8,000, which itself is calculated as...
    • 2 Engines @ $1,500 = $3,000
    • 100 Bolts @ $10 = $1,000
Also, ordering a Boing 747 would affect Product qtyOnHand as follows:
  • totalQtyOrdered[Engines] increased by 4 (2 for each of 2 wings)
  • totalQtyOrdered[Bolts] increased by 300 (100 from Boing, 100 for each of 2 wings)
Ordering the Boing 747 inserts the following LineItems:
  • Boing 747
    • Bolts
    • Wing
      • Bolts
      • Engine
    • Fuselage

Kahuna Sample Diagram.png
Max Tardiveau,
May 8, 2013, 12:28 AM