One thing that we can all be certain of when it comes to relationships of any kind in life is that chances are, they will be complicated. Complicated isn't to say the relationship itself is bad, inappropriate, or won't flourish; it just means it may take some extra work.

This simple fact remains true for Python and the different relationships we encounter when building a complex yet effective applications.

Types of Relationships in Python

There are many different relations in python but I will be touching on the two that I've encountered most as I'm finishing up my first python CLI application.

Many-to-many

In order to describe a many-to-many relationship, I will use the model from the CLI application that I built. In my particular CLI which was kept simple and to the point, I used a one-to-many relationship (which I will cover next) but for the sake of this example I will change my tables slightly. I created an "event manager" application that displays data for athletes, and their corresponding events. If I were to use a many-to-many relationship for this, I would say an athlete can participate in *many events(or races) and an event(or race) can have many athletes. The data for these tables is interacted with and queried using SQL and SQL explorer. (For the sake of this blog I will not cover that in depth, but here's a good reference blog if you're interested https://dev.to/harperdb/sql-and-relational-databases-explained-by-a-non-developer-e3a. The tables would be: an event table which would hold the data for each event(or race) such as name, date, and location, and an athlete table which would hold the data for each athlete, such as name, age, and gender. A third table, called an association table, which we will call Athlete_event in this example, would hold the data for the specific athlete(by ID) and the corresponding event(by ID).
This table is used to show the many-to-many relationship between athletes and events, which may also be referred to as a "joint table".

for Example:

CREATE TABLE Athlete (
athlete_id INT PRIMARY KEY,
name VARCHAR(100),
age INT,
gender CHAR(1)
);

CREATE TABLE Event (
event_id INT PRIMARY KEY,
name VARCHAR(100),
date DATE,
location VARCHAR(100)
);

CREATE TABLE Athlete_Event (
athlete_id INT,
event_id INT,
FOREIGN KEY (athlete_id) REFERENCES Athlete(athlete_id),
FOREIGN KEY (event_id) REFERENCES Event(event_id),
PRIMARY KEY (athlete_id, event_id)
);

This would effectively display a one-to-many relationship.

One-to-Many

One-to-many is a little less complex, yet still a slightly mind boggling to think about. In my Event Manager application, an athlete could participate in one event(or race) and an event(or race) could have many athlete's participating in it(obviously, or else, what kind of race would that be?!)

CREATE TABLE Event (
event_id INT PRIMARY KEY,
name VARCHAR(100),
date DATE,
location VARCHAR(100)
);

CREATE TABLE Athlete (
athlete_id INT PRIMARY KEY,
name VARCHAR(100),
age INT,
gender CHAR(1),
event_id INT,
FOREIGN KEY (event_id) REFERENCES Event(event_id)
);

The 'Foreign key' is the event_id in the Athlete table, which references the event that the athlete participated in. In the event table, this specific event_id is a primary key. When it is referenced in another table, in this case the athlete table, it is referred to as the foreign key.

While this particular blog just skims the surface of One-to-Many and Many-to-Many relationships, I hope it has given you a basic understanding of the types of relationships we can explore and build in order to handle data as efficiently and easily as possible.

SOURCES:

• https://www.pinterest.com/pin/735142339164545850/

• https://dev.to/harperdb/sql-and-relational-databases-explained-by-a-non-developer-e3a

• https://medium.com/@aobrienaf/a-look-into-many-to-many-and-one-to-many-relationships-in-python-3-152243c682d7