Tutorial‎ > ‎

Database Structure

This describes the database structure, and the relevant Hibernate elements.


 
==> Note: you can open the image in another window to view while reading
 


Database Structure

The Database is familiar and small, yet includes a number of subtle elements worth investigation.



Bill of Materials Structure

The sample database contains several Products:

Name Price PartNum
Boing (10,300) 12
Fuselage 1300 13
Wing (4000) 14
Engine 1500 15
Bolt 10 16


Now, let's say that:

  • a Boing consists of a Fuselage, Wing and Bolts

  • a Wing consists of an Engine and Bolts

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

PartNumKit PartNumber ProductNameKit ProductName ComponentPrice KitNumberRequired Value
12 13 Boing Fuselage 1500 1 1300
12 16 Boing Bolt 10 100 1,000
12 14 Boing Wing 4000 2 8000
14 15 Wing Engine 1500 2 3000
14 16 Wing Bolt 10 100 1000


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 ProductBillofmaterials:

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

  • components: kit implemented by foreign key PartNumKit - so...
    • product.components returns the ProductBillofmaterials 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[15/Engines] increased by 4 (2 for each of 2 wings)
  • totalQtyOrdered[16/Bolts] increased by 300 (100 from Boing, 100 for each of 2 wings)
Ordering the Boing 747 inserts the following LineItems:
  • [11]: the Boing 747
    • [12]: Bolts
    • [13]: Wing
      • [14]: Bolts
      • [15]: Engine
    • [16}: Fuselage

Department / Employee Structure

There are a number of interesting elements to this structure, as described in the subsections below.

Recursive relationship

Each Department can have Sub-Departments, implemented by the HeadDeptId.  Business Logic enables you to implement a Budget Rollup.


Multiple Relationships

There are multiple relationships between Department and Employee.  Note the use of Role Names to enable you to reference the intended relationship.


Inheritance

EmpSales extends Employee.


Database Implementation

Domain Objects (POJO's)

Hibernate hbm files



Logicdoc Class Diagram

The diagram at the top of this page was produced by MySql Workbench. Other tools offer similar services. Logicdoc provides another mechanism to generate similar diagrams, with automatic layout:




Comments