Define the Relationship

On the previous page about “many_to_many” relationships, we showed that a join table is needed to sit between 2 tables that have foreign references to both. We also talked about how some ORMs are short sighted when it comes to these types of relationships. Rather than having a join table with only the foreign keys and relying on the ORM to handle everything, it’s better to treat the join table as a first class citizen and promote it to an actual model. For example: 

CREATE TABLE hashtags_web_pages
(
  id SERIAL NOT NULL 
    CONSTRAINT hashtags_pkey
      PRIMARY KEY,
  hashtag_id INT
    CONSTRAINT fk_web_page_hashtags
      REFERENCES hashtags(id),
  web_page_id INT
    CONSTRAINT fk_hashtag_web_pages
      REFERENCES webpages(id)
);

When relying on an ORM like ActiveRecord to handle this, it tends to run far more queries than is needed. It also prevents the relationship from having attributes of its own.

Complex Relationships

On the Schema Design page, we talked about how verbs become relationships. For example:

In a basic relationship, such as when the verb is “has” or “is”, the rules for a normal relationship applies; for example, you should use a normal “has_many” relationship if a person has one or more addresses.

Relationships aren’t always so simple. Take the case of “A person bought a car”. A person could buy more than one car, so this is technically a “has_many” relationship. The key here is the word “bought”. If it were, “A person has one or more cars”, that is exactly what would be needed.

The difference is between the word “has” and the word “bought”. To properly record that something was bought, we need more info such as the price and the day it was purchased. We might also want to know who sold it or if the car was later sold.

All of these attributes belong on the join table. In a graph database, there aren’t tables and columns. Objects are called nodes, and the relationships between these objects are called edges. Attributes on an edge are called a facet. Therefore, a relational database would have extra columns on the join table, while a graph database would put that data in facets on an edge.

In the following schema, the join table is called car_purchases. It contains the foreign keys to the persons and cars tables and actually joins twice to the persons table to indicate the buyer and the seller. It also contains columns for the price and when it was sold. This way, every time the same car is sold, it can be cataloged independently. 

CREATE TABLE persons
(
  id SERIAL NOT NULL 
    CONSTRAINT person_pkey
      PRIMARY KEY,
  first_name VARCHAR(255),
  last_name VARCHAR(255)
);

CREATE TABLE cars
(
  id SERIAL NOT NULL 
    CONSTRAINT car_pkey
      PRIMARY KEY,
  year INT,
  make VARCHAR(255),
  model VARCHAR(255),  
  vin VARCHAR(255)
);

CREATE TABLE car_purchases
(
  id SERIAL NOT NULL 
    CONSTRAINT car_purchase_pkey
      PRIMARY KEY,
  buyer_person_id INT
    CONSTRAINT fk_buyer_car_purchase_persons
      REFERENCES persons(id),
  seller_person_id INT
    CONSTRAINT fk_seller_car_purchase_persons
      REFERENCES persons(id),
  car_id INT
    CONSTRAINT fk_car_purchase_cars
      REFERENCES cars(id),
  price DECIMAL(10,2),
  sold_at TIMESTAMP(0)
);