|This provides a brief background of key database concepts, including the terminology we use.
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.
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 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.
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 theCustomer = anOrder.customer
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
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.
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)
, where Department has a Foreign Key
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
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
, 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.