Docs‎ > ‎Integrate‎ > ‎Data Sources‎ > ‎

Database Concepts


This provides a brief background of key database concepts, including the terminology we use.






Database Diagrams

Although not required, it is a Best Practice to maintain a diagram of your Domain Objects, as shown above.

You can produce such diagrams from a database tool (such as MySqlWorkbench™ above).

In either case, nodes represent Tables (aka Entities), and lines represent Relationships, also often referred to as Associations.


Table - no repeating lists of values

Tables correspond to "domain objects," but what you think of as a domain object is (quite often) several tables.  For example, you may think an of Order as a table, but it's really 2: Order and Items.   A key idea is that column values cannot be a repeating list - here, the items purchased in an order is a repeating list, so it must be broken out into a child table.

Keys

In the vast majority of cases, database rows are identified by a key - one or many columns that uniquely identify a row.  In most cases, key is taken to mean "unique" key, which is named and identified to the dbms.

There are two common approaches:
  • Natural Key - choose a key from attributes (columns) that exist in the real world

  • Surrogate Key - let the database assign a unique id for a row (named differently in different databases, for example "Identity" in MySql).

Relationships

Relationships are a foundation of Data Modeling. Although there are many kinds, the most fundamental concept is a one-to-many relationship. These are sometimes called hasA relationships.

Represented by a line (arc) in the diagram, it represents one Parent object that is related to many Child objects. For example, one Customer has many Purchaseorders.

In relational databases, you define a Foreign Key in the child to the Parent. So, Purchaseorder has an attribute CustomerName.

Parent Role

A Parent Role is the name a Child uses to access its parent within a one-to-many relationship. In the example above, customer represents the Customer for a given Purchase Order.

 API Creator creates an Object Model, providing access to related data (caching is provided to optimize repeated access):

var anOrder = row.order;
var theCustomer = anOrder.customer



Children Role

A Children Role is the name a Parent uses to access a collection of related children within a one-to-many relationship. In the example below, orders represents the Orders for a given Customer.

The object model provides access to child data:

var customerOrders = aCustomer.orders;
                     
for (var i = 0; i < customerOrders.length; i++)
   log.debug('Found order:' + customerOrders[i].amountTotal);


Terminology Note: what the accessor returns

While the concept is usually quite obvious, the terminology can be troublesome - we need to choose between these two reasonable (but opposite!) explanations for what is a child role?
  • origin-based: is a child role a role in a child (that gets parents), or
  • returns-based: is it in a parent (that gets children).

To make this easy to remember:
  • Role Names correspond to accessor names, so row.<Parent> returns a parent, is a parent accessor


Relationship Examples

The sample database contains classic examples of the kinds of Parent/Child relationships present in virtually all databases. In the parlance below, 1:n means one to many.


1:n / different

This is the most common, where there are different objects at both ends of the relationship. The classic example is Customer/Purchaseorder, where Purchaseorder contains a foreign key of the Customer.

1:n / same ("self relationship")


Here the Parent and Child are the same type - but not the same instance. The classic example is Departments which have (Sub) Departments, where Department has a Foreign Key head_department_name.   

For example, the CEO Department has Sub Departments of Sales, and Engineering.  Self-relationships recurse, so Sales has its own Sub Departments such as International Sales and Domestic Sales.

Note: Transitive Closure is a retrieval concept that means get all related objects and subobjects, recursively.

n:m / different

Here there are many objects on both sides of a relationship.  Relational database does not support these directly, since a Foreign Key (like any attribute) can have only 1 value. 

So, you introduce Junction (also called Intersection) Objects to represent these. For example, a Orders can have many Products, and a Product can be ordered on many Orders. We introduce the Junction Lineitems which as Foreign Keys to both, as well as additional attributes (such as quantity_ordered).

n:m / same

These are less common, and rather complicated. The classic example is a Bill of Materials explosion, where Products can be Kits: comprised of multiple Component Parts (which recurses - Components can have Sub Components).  Conversely, a Component Part can be a part of multiple Kit Parts. 

As for n:m / different, you introduce a Junction table product_billofmaterials, which has two Foreign Keys to the Parent. See Bill of Materials Structure.

As things get more complex, there can be multiple relationships between the same tables.