What is an accurate source to learn about associations and directionality in SQL?

Question

I am unable to find an easy to understand and complete source which explains the concepts of associations and directionality. I even read the Hibernate documentation, but that goes into the implementation, rather than the conceptual knowledge, which I am seeking to solidify.

There seems to be so much misinformation and "rule of thumb" knowledge on the topic.

For example: you will hear people claiming that the child table is always the "many" side, when we know this is not always the case.

Another example: Consider the Products table, and the Invoice table. Let's assume that we want this to be a many-to-one unidirectional relationship because we are only concerned with what products an invoice contains, as opposed to each invoice a product appears on. But what if there is a recall, and we need to find all those invoices which contain that particular product?

I've attached a cheat-sheet that I have been working on to have all of this make sense, but it's been frustrating dealing with the contradictions and ambiguity. Please guide me in the right direction.

Thank you.

See [this re the relational model vs the original ER, pseudo-ER and ORM methods/diagrams](http://stackoverflow.com/a/27272088/3404097) & [this re lines as associations/relationships](http://stackoverflow.com/a/34427859/3404097). Look at Object Role Modeling aka ORM2; an ORM2 design directly corresponds to a relational design and there is a chapter in both editions of Halpin's [Information Modeling and Relational Databases](http://www.orm.net/resources.html) that maps ORM2 diagrams to diagrams in other methods. — philipxy, Jul 01 '16 at 04:04
@EbonyMaw I agree with philipxy and clifton_h's recommendations to study proper information modeling disciplines. Note that this may not help you much with Hibernate, which is based on the network data model. — reaanb, Jul 01 '16 at 06:03
@reaanb Yes, I think I'm in a situation where I "don't know what I don't know," and that's a very anxiety provoking position to be in...but getting my terminology straight would be the first step... — Ebony Maw, Jul 01 '16 at 06:39
'the child table is always the "many" side' uses "parent", "child" and "relationship" in a certain way. (And it's *not* in the sense of an application relationship/association/table being in some sense directed from some parameters/columns to others.) A foreign key that holds in a database gets called a "relationship" (between tables) (although it's not) and "child" the referencing table & "parent" the referenced table. (When a foreign key holds, values for a referencing table & columns must also appear as values for a referenced table & columns.) For this usage, the quote is correct. — philipxy, Jul 01 '16 at 10:48
@EbonyMaw check out my updated post, I think you will find it useful. — clifton_h, Jul 01 '16 at 10:52

clifton_h · Accepted Answer · 2016-07-01T10:58:05.153

UPDATE:

As it turns out, I had not noticed the Hibernate tag in your question, being confused by the conflicting, OOP-based terminology you were using in the question about the database layer.

HIBERNATE SQL is special form of JAVA:

TutorialsPoint

Hibernate is an Object-Relational Mapping(ORM) solution for JAVA that acts as an open source persistent framework between the application layer and database server. it raised as an open source persistent framework.

While database languages like SQL are concerned about the relationships between tables and their columns, HIBERNATE is concerned with JAVA objects to and from the database.

HIBERANATE SERVES AS A BRIDGE WITH OR WITHOUT AN APPLICATION LAYER

Even though all good OOP languages take advantage of inheritance and classes that provide abstraction of machine/lower languages, HIBERNATE offers a solution to standard data persistence problems by being an abstract layer between unfamiliar SQL data types and the JAVA objects in general.

As it happens, database languages like T-SQL and MYSQL use XML markup languages which HIBERNATE takes advantage, providing persistent services and objects without the need to interact with lower APIs.

HIBERNATE IS NOT A DBMS BUT ALLOWS FOR SMART FETCHING STRATEGIES IN BOTH SESSIONS AND TRANSACTIONS OBJECTS

This is what I surmised was the confusion between OOP and relational database design theory:

Paul Nielsen - NORDIC OBJECT/RELATIONAL DATABASE DESIGN (excerpt from MSDN)

The differences between object-oriented development and the relational database model create a tension often called the object-relational impedance mismatch. Inheritance just does not translate well into a relational schema.

The technical impedance mismatch is aggravated by the cultural disconnect between application coders and database administrators (DBAs). Often neither side fully understands nor respects the other's lexicon. A sure way to get under a DBA's skin is to refer to the database as the "object persistence utility." That relationship is unfortunate because each side brings a unique set of advantages to the data architecture problem.

This is why MSDN, in fact, never uses the term "directionality" in any of their documents. It simply is not relevant to the way SQL works relationally. Certainly one can have a uni- or bi-directionality of in a table relation, but SQL is more than that, offering a very flexible, albeit limited ability to compare datasets as a whole by their keys rather than row by row as a cursor (though there is functionality for this).

I like to think the latter like a book, which has chapters and pages as keys that identify the associate of each part of the book. At no point will I think parts of the book are separate from each other. They all are relational to each other.

CARDINALITY AND NORMALIZATION

Two major terms you will hear is Cardinality (number of distinct items associated to a given set) and Normalization (how tolerant the database is of redundant information). If a table's columns contains information that is indivisible of its category and does not repeat itself in the other columns, we call this 1NF. As we progress to the other forms, we remove identity and remove associations that are not atomic, or are partial or completely functional dependencies, transitive, historical and so on in their own tables.

Because NF theory offers a fairly consistent definition, describing relationships and dependencies in the database can be easier to follow:

A relvar (table) R is in 6NF if and only if R satisfies no nontrivial join dependencies at all, in which case R is said to be irreducible.” (Date et al., 2002)

Redundancy being the villain here, normalization nonetheless depends on the business needs. You might have heard of the Star Schema or Snow Flake methods, which describe more of the architectural strategy than of normalization.

Without getting into too much detail, there are two strain of thoughts on database design, the IMAN and KIMBALL methods, which simplistically (don't throw rocks at me people) can be likened to the Agile vs Waterfall design methodologies, only in the sense that Iman used the many existing pieces to design the data warehouse while Kimball thought himself visionary by using the abstract business needs to design the data marts for the warehouse. Each style is correct in their own way.

PILLARS OF DATABASE THEORY

Lastly, it is crucial to remember what the main goals and properties that all SQL languages have in what is referred to as **ACID**.

Atomic: SQL performs its queries in a transaction entirely or not at all. If a query runs into a fatal error, the entire transaction is rolled back and nothing is committed/written.
Consistent: SQL has strict rules in its definitions and tables. Any invalid data or action that would violate the database and leave SQL in an unstable state, will result in a ROLLBACK to the last stable state.
Isolation: Multiple transactions run at the same time occur independently of each other. If the transactions occur on the same part of the database, SQL ensures, like cars in a one way street, that each transaction gets its turn. ISOLATION levels can be set to determine if dirty reads (incomplete) occur.

HOWEVER:, since ISOLATION forces one transaction to wait for another transaction to finish accessing a part of the database, DEAD LOCKS can occur when those transactions end up waiting for each other indefinitely. Do not let this happen on a live server.

Durability: Unlike OOP languages, SQL ensures that any transaction committed to the database will not be lost. Backups and logs allows SQL to finish or even restore transactions even in the event of a power-loss or system failure.

Extra note: The IDEF1X standard makes a fantastic study and read, and even Derek Asirvadem's pamphlet is worth browsing at the least for inspiration. Note IDEF1X is founded on the original work of Dr. E.F. Codd on Relational Model