Database Concepts

Database Concepts
lac31
 
CA Live API Creator
 includes the following database concepts and 
CA Live API Creator
 terminology:
 
 
2
 
 
Database Diagram
The following diagram provides a background of key database concepts, including 
CA Live API Creator
 terminology. Nodes represent entities (tables and views) and lines represent associations, or relationships:
  Kahuna%2520Sample%2520Diagram.png  
Best Practice:
 Maintain a diagram of your domain objects.
You can produce database diagrams from a database tool, such as MySQL Workbench.
Table - No Repeating Lists of Values
Tables correspond to "domain objects." But what you think of as a domain object is (often) several tables. For example, you might think of an Order as a table, but it is really two: Order and Items. Column values cannot be a repeating list. In this example, the items purchased in an order is a repeating list and must be broken out into a child table.
Keys
Usually, 
CA Live API Creator
 identifies database rows by a key, one or many columns that uniquely identify a row. Usually, key is taken to mean "unique" key, which is named and identified to the database management system (DBMS).
The following approaches are common:
  •  
    Natural key
     
    .
     Choose a key from attributes (columns) that exist in the real world.
    For more information about natural keys, see Natural key on the Wikipedia website.
  •  
    Primary key/surrogate key.
     Let the database assign a unique ID for a row (named differently in different databases, for example "Identity" in MySQL).
    For more information about primary key/surrogate key, see Automatic Key Generation.
Relationships
Relationships are a foundation of data modeling: a one-to-many relationship between two tables in a relational database. API Creator discovers relationships using the foreign keys that are defined in your database. Relationships are sometimes called 
hasA
 relationships. Represented by a line (arc) in the previous diagram, it represents one parent object that is related to many child objects.
 
CA Live API Creator
 requires references to foreign key-based relationships to express logic (for example, sum/count and parent references), retrieval joins, JavaScript programming with the JavaScript object model, Data Explorer support for master/detail, lookups, and automatic joins. You must define relationships and you must define them properly. Within a relationship, the table containing the primary key on the "one" side, for example,
Purchaseorder
, is a parent to the child called
Lineitem
. PUT/POST JSON provides mechanisms to associate a child with its parents.
 Some databases, such as Microsoft SQL Server, support foreign keys across schemas. Schema discovery deals with only one schema at a time, so these foreign keys are not discovered. You can define the relationship between databases by adding virtual foreign keys.
For more information about how to define relationships between databases by adding a virtual foreign key, see Integrate Multiple Databases.
 For more information:
The following sections describe how 
CA Live API Creator
 computes role names for relationships in three cases:
  • The first line represents a managed database. Role names are driven by the 
    relationshipName
     that is created in the Data Explorer.
    For more information about role names for managed databases, see Managed Database Architecture.
  • The second line represents a database that is not managed. Role Names are driven by the foreign key (
    fKeyName
    ) found in the database.
  • The deprecated column represent a database that is not managed.
    For more information about the deprecated role name strategy, see Database Connectivity.
 
CA Live API Creator
 computes role names as follows:
Name
Standard
Example
Deprecated
Example
Usage
 
Parent Role
 
<relationshipName>_<parent>
<fKeyName>_<parent>
OwnedBy_Customers
customer_customers
<ForeignKey>
customer
customer
Parent access (from child row)
 
Child Role
 
<relationshipName>_<child>_"List"
<fKeyName>_<child>_"List"
OwnedBy_Orders_List
customer_orders_List
<ChildTableName> + "List"
if multiple: <ForeignKey>+<ChildTableName>+"List"
OrdersList
Child access (from parent row)
 
Foreign Key
 
<relationshipName>_<random>
FK_OwnedBy_$1234$
(Supplied by DBMS)
 
Internal (for DBMS)
 
Foreign Key Column
 
<parent>_ident
Customers_ident
(Supplied by DBMS)
n/a
Internal (for DMBS)
In complex examples (for example, an Employee might work for one department and might be on loan to another), you must alter these default role names so that the foreign key name is unique. You can alter these names as desired using your favorite database tools.
Parent Role
A parent role is the name a child role uses to access its parent within a one-to-many relationship. In the previous example, customer represents the Customer for a given purchase order.
API Creator creates a JavaScript object model, providing access to related data (caching is provided to optimize repeated access) by using the foreign key name, for example:
 
var theCustomer = anOrder.Customer;
For more information about the JavaScript object model, see Row Objects.
Children Role
A children role is the name a parent role uses to access a collection of related children within a one-to-many relationship. In the following example, orders represents the Orders for a given Customer.
The JavaScript object model provides access to child data by using:
<Child Table Name> + "List":
For more information about the JavaScript object model, see Row Objects.
API Creator normalizes <Child Table Name> to include only alpha, numeric, and underscore.
Duplicate Child Role Names
If there are multiple relationships between two tables, the previous default algorithm results in duplicate names. API Creator disambiguates the names by computing the child role name as:
ForeignKey + <ChildTableName> + List
For example, the employees table has two foreign key validations to department, specified in the schema, for example:
CONSTRAINT reportsTo FOREIGN KEY (department_name) REFERENCES departments (name) ON DELETE....,
CONSTRAINT onLoanTo FOREIGN KEY (on_loan_department_name) REFERENCES departments (name) ON DELETE ...
API Creator computes the child role names:
 
count(reportsToemployeesList) count(onLoanToemployeesList)
You can encode role names into the foreign key using double underscore ("__", shown as blanks in the following example) to separate 
parentRole
 from 
childRole
:
 
CONSTRAINT
reportsTo__ReportingEmployees
FOREIGN KEY (department_name) REFERENCES departments (name) ON DELETE...., CONSTRAINT
onLoanTo__OnLoanEmployees
FOREIGN KEY (on_loan_department_name) REFERENCES departments (name) ON DELETE ...
Terminology Note: What the Accessor Returns
While the concept is obvious, the terminology can be troublesome. There are two reasonable (but opposite!) explanations to defining a child role:
  • Origin-based. The child role is a role in a child (that gets parents).
  • Returns-based. The child role is in a parent (that gets children).
Role names correspond to accessor names.
row.<Parent>
returns a parent and is a parent accessor.
Role Name Strategy
For more information about the role name strategy for your API, see Database Connectivity.
Relationship Examples
The Logic Sample database includes classic examples of the kinds of parent-child relationships present in virtually all databases.
The following sections illustration key parent-child relationships.
For more information:
  • About associating a child with its parent using PUT/POST JSON mechanisms, see PUT.
  • About the Logic Sample database, see Logic Sample Database.
One-to-many/Different Relationship
The one-to-many/different parent/child relationship 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.
 The following image shows an example of this relationship from the Logic Sample database:
  CA Technologies
One-to-many/Same (Self) Relationship
The one-to-many/same parent-child relationship, or self relationship, includes a parent and child of the same type, but not the same instance. The classic example is Departments which have (Sub) 
Departments
, where Department has a 
head_department_name
 foreign key.
For example, the CEO Department has Sub Departments of Sales, and Engineering. Self-relationships recurse. Sales has its own Sub Departments such as International Sales and Domestic Sales.
Transitive Closure is a retrieval concept that means get all related objects and sub-objects, recursively.
The following image shows an example of this relationship from the Logic Sample database:
CA Technologies  
Many-to-many/Different Relationship
The many-to-many/different parent-child relationship includes many objects on both sides of a relationship. Relational databases do not support these relationships directly, since foreign key (like any attribute) can have only one value. You can introduce junction (link) tables to represent these.
For example, in the LogicSample database, 
Orders
 can have many 
Products
 and a 
Product
 can be ordered on many 
Orders
. This sample database introduces the junction 
Lineitems
 table, which has foreign keys to both, as well as additional attributes (such as 
quantity_ordered
).
The following image shows an example of this relationship from the Logic Sample database:
CA Technologies  
For more information:
Many-to-many/Same Relationship
This many-to-many/same parent/child relationship is 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. You introduce a 
product_billofmaterials
 junction table, which has two foreign keys to the parent. As things get more complex, there can be multiple relationships between the same tables.
The following image shows an example of this relationship from the Logic Sample database:
CA Technologies