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.
On the Schema Design page, we talked about how verbs become relationships. For example:
A person has one or more addresses.
A person has a mother.
A person bought a car.
A person paid rent.
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) );