DEV Community

Cover image for One-To-Many Database Relationships Complete How To
brandon_wallace
brandon_wallace

Posted on • Updated on

One-To-Many Database Relationships Complete How To

INTRODUCTION

In this article I will show you how to set up a one-to-many database relationship, how you would know when you would need to use one, as well as some example queries.

We will use:

  • Python 3.9+
  • PostgreSQL (database)
  • Flask (framework)
  • Flask-SQLAlchemy (object-relation mapper)
  • Pipenv (virtual environment)

Once your C.R.U.D. ( Create Read Update Delete ) applications start getting more complex, you will find that you need to link tables together within the database. In databases it is very common task to create relationships between tables. A one-to-many relationship is a method that will allow you to link a parent to a child table in a database. A one-to-many relationship is created by adding a foreign key to the child table.
one-to-many-erd-generic.png

Diagram created on diagrams.net
PK = primary key
FK = foreign key

WHEN WOULD I USE A ONE-TO-MANY RELATIONSHIP?

Let me present to you a real-world scenario, let’s say you have an online store and you need to keep track of your customers and the orders they make. In your database you would create a table for customers and separate table for the orders. How would you know which order belongs to which customer? The way to do that is to set up a one-to-many relationship. This will allow you to take one customer and query all the information connected to the orders the customer made.

one-to-many-erd.png

I will show you an easy way to know when you need to set up a one-to-many database relationship as opposed to a one-to-one or a many-to-many database relationship. Going back to the example of the online store, you have two tables, a customer table and an order table. You would ask yourself two questions about the tables.

Example 1:

Q: Does a customer have many orders?
A: Yes, a customer can have many orders.

Q: Does an order have many customers?
A: No, only one person can create an order.

We have one customer and many orders but it does not work the other way around. One order does not have many customers. We can clearly see that example 1 is a great candidate for a one-to-many database relationship.

Let’s look at another example.

Example 2:

In another imaginary database we have a table for students and a table for classes. So we form the questions like this.

Q: Does a student have many classes?
A: Yes, a student can have many classes.

Q: Does a class have many students?
A: Yes, a class can have many students.

Here we have many students and many classes because of that, example 2 is not suitable for a one-to-many relationship.

How To Set Up The Project

Install PostgreSQL and Pip. We use Pip to install Pipenv.

$ sudo apt install postgresql libpq-dev python3-pip
Enter fullscreen mode Exit fullscreen mode

Enable and start PostgreSQL.

$ sudo systemctl enable postgresql

$ sudo systemctl start postgresql
Enter fullscreen mode Exit fullscreen mode

Check to see if PostgreSQL is running.

$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Mon 2021-02-01 10:09:46 EST; 2h 56min ago
   Process: 920 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 920 (code=exited, status=0/SUCCESS)
[...]
Enter fullscreen mode Exit fullscreen mode

Log in as the postgres user and then the interactive PostgreSQL terminal 'psql'.

$ sudo su – postgres

postgres@laptop:~$ psql

postgres=#
Enter fullscreen mode Exit fullscreen mode

Check the PostgreSQL version.

postgres=# SELECT version();
                                                     version                                                      
------------------------------------------------------------------
 PostgreSQL 14.1 (Debian 14.1-1.pgdg110+1) on x86_64-pc-linux-gnu [...]
Enter fullscreen mode Exit fullscreen mode

Set the postgres user’s password. Please use something stronger than 123456.

postgres=# ALTER USER postgres WITH PASSWORD '123456'; 
ALTER ROLE       
Enter fullscreen mode Exit fullscreen mode

Create a database called mystoredb.

postgres=# CREATE DATABASE mystoredb;
CREATE DATABASE
Enter fullscreen mode Exit fullscreen mode

List the databases. You will see mystoredb on the list.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 mystoredb | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
[...]
Enter fullscreen mode Exit fullscreen mode

Exit psql interactive terminal and logout as the postgres user.

postgres=# \q

postgres@laptop:~$ exit
Enter fullscreen mode Exit fullscreen mode

Create a directory for your project.

$ mkdir my_project/

$ cd my_project/
Enter fullscreen mode Exit fullscreen mode

Install pipenv using pip3.

$ pip3 install pipenv
Enter fullscreen mode Exit fullscreen mode

Start the Python virtual environment.

$ pipenv shell
Enter fullscreen mode Exit fullscreen mode

Install the required libraries.

$ pipenv install flask flask-sqlalchemy psycopg2-binary
Enter fullscreen mode Exit fullscreen mode

Here is a simple example of a one-to-many relationship with Flask-SQLAlchemy.

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    comment_id = db.relationship('Comment', cascade='all, delete', backref='author' lazy=True)

class Comment(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    post_id = db.Column(db.Integer, db.ForeignKey('post.id'))
Enter fullscreen mode Exit fullscreen mode

db.relationship()

This function looks at the child table and pulls up multiple instances of the child table.

backref

Backref creates a kind of invisible column within the child table.

lazy

Lazy defines when the data will be pulled from the database.

lazy=select
or
lazy=True

This is the default setting. SQLAlchemy will load the all data necessary at once.

db.ForiegnKey()

This function specifies the parent class (written in lowercase) and relates items back to the parent table.

Create file called application.py and add the following content.

$ vim application.py

# application.py

from datetime import datetime
from flask import Flask
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)

app.config['DEBUG'] = True
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://postgres:123456@127.0.0.1:5432/mystoredb'

db = SQLAlchemy(app)


class Customer(db.Model):
    '''Customer table'''

    __tablename__ = 'customer'
    id = db.Column(db.Integer, primary_key=True)
    customer_name = db.Column(db.String(25))
    customer_email = db.Column(db.String(100), nullable=True)
    order_id = db.relationship('Order', cascade='all, delete', backref='customer', lazy=True)

    def __init__(self, customer_name, customer_email):
        self.customer_name = customer_name
        self. customer_email = customer_email

    def __repr__(self):
        return f'<Customer: {self.id} {self.customer_name}>'


class Order(db.Model):
    '''Order table'''

    __tablename__ = 'order'
    id = db.Column(db.Integer, primary_key=True)
    order_date = db.Column(db.DateTime, default=datetime.now)
    # Use datetime to generate a random 6 digit number from milliseconds.
    order_number = db.Column(db.Integer, default=datetime.now().strftime("%f"))
    customer_id = db.Column(db.Integer, db.ForeignKey('customer.id'))

    def __repr__(self):
        return f'<OrderID: {self.id}>'


if __name__ == '__main__':
    app.run(debug=True)
Enter fullscreen mode Exit fullscreen mode

*NOTE:

The syntax for the SQLALCHEMY_DATABASE_URI variable is

'postgresql://<db_username>:<password>@<ip_address>:<port>/<database_name>'

Example of what I am using in this tutorial:

'postgresql://postgres:123456@127.0.0.1:5432/mystoredb'
Enter fullscreen mode Exit fullscreen mode

Change the values of the database URI to reflect your set up.

Start a Python interpreter shell on the command line.

$ python3
Enter fullscreen mode Exit fullscreen mode

Import the database + models and create the tables.

>>> from application import db, Customer, Order

>>> db.create_all()
Enter fullscreen mode Exit fullscreen mode

Add a few people to the customer table.

>>> brandon = Customer(customer_name='Brandon', customer_email='brandon@example.com')
>>> maria = Customer(customer_name='Maria', customer_email='maria@example.com')
>>> ahmed = Customer(customer_name='Ahmed', customer_email='ahmed@example.com')
>>> cheuk = Customer(customer_name='Cheuk', customer_email='cheuk@example.com')
>>> nikolaus = Customer(customer_name='Nikolaus', customer_email='nikolaus@example.com')
Enter fullscreen mode Exit fullscreen mode

Add all people to the database session.

>>> db.session.add_all([brandon, maria, ahmed, cheuk, nikolaus])

Enter fullscreen mode Exit fullscreen mode

Commit all changes to the database to save the users.

>>> db.session.commit()
Enter fullscreen mode Exit fullscreen mode

Create orders and link them with customers in the database like this. Remember 'customer' is the backref value in the parent table.

>>> order1 = Order(customer=brandon)
>>> order2 = Order(customer=brandon)
>>> order3 = Order(customer=brandon)
>>> order4 = Order(customer=ahmed)
>>> order5 = Order(customer=ahmed)
>>> order6 = Order(customer=cheuk)
>>> order7 = Order(customer=cheuk)
>>> order8 = Order(customer=maria)
>>> order9 = Order(customer=nikolaus)
Enter fullscreen mode Exit fullscreen mode

Add all orders to the session.

>>> db.session.add_all([order1, order3, order3, order4, order5, order6, order7, order8, order9])
Enter fullscreen mode Exit fullscreen mode

Commit all changes to the database to save the orders.

>>> db.session.commit()
Enter fullscreen mode Exit fullscreen mode

How To Perform Queries

At this point we can query the database.

Pull up all the customer records in the database and loop through the results.

>>> customers = Customer.query.all()

>>> for person in customers:
...     f'ID: {person.id} Name: {person.customer_name} Email: {person.customer_email}'
... 
'ID: 1 Name: Brandon Email: brandon@example.com'
'ID: 2 Name: Maria Email: maria@example.com'
'ID: 3 Name: Ahmed Email: ahmed@example.com'
'ID: 4 Name: Cheuk Email: cheuk@example.com'
'ID: 5 Name: Nikolaus Email: nikolaus@example.com'
Enter fullscreen mode Exit fullscreen mode

Extract the name like this.

>>> for customer in customers:
...     customer.customer_name
... 
'Brandon'
'Maria'
'Ahmed'
'Cheuk'
'Nikolaus'
Enter fullscreen mode Exit fullscreen mode

Get the total order count for Brandon.

>>> brandon_order_count = Order.query.filter_by(customer_id=1).count()

>>> f'Total orders for Brandon: {brandon_order_count}'
Total orders for Brandon: 3
Enter fullscreen mode Exit fullscreen mode

Get the order numbers for Brandon.

>>> brandon_all_orders = Order.query.filter(Order.customer_id == 1).all()

>>> for order in brandon_all_orders:
...     f'Order number: {order.order_number}'
... 
'Order number: 680018'
'Order number: 193492'
'Order number: 662450'
Enter fullscreen mode Exit fullscreen mode

Get the orders from Brandon sorted by most recent.

>>> latest_orders = Order.query.order_by(Order.order_date.desc()).all()

>>> for order in latest_orders:
...     order.order_date.strftime("%Y-%m-%d %H:%M")
... 
'2022-04-03 18:09'
'2022-02-02 20:22'
'2022-01-01 10:59'
Enter fullscreen mode Exit fullscreen mode

Get the orders from Brandon sorted by oldest first.

>>> oldest_orders = Order.query.order_by(Order.order_date.desc()).all()

>>> for order in oldest_orders:
...     order.order_date.strftime("%Y-%m-%d %H:%M")
... 
'2022-01-01 10:59'
'2022-02-02 20:22'
'2022-04-03 18:09'
Enter fullscreen mode Exit fullscreen mode

Get orders filtering by a before a specific date.

>>> from datetime import datetime
>>> from_date = datetime(year=2022, month=2, day=22)

>>> befor_feb_22 = Order.query.filter(Order.order_date <= from_date).all()

>>> for order in before_feb_22:
...     f'ID: {order.id} Date: {order.order_date} OrderNum: {order.order_number} CustID: {order.customer_id}'
... 
'ID: 2 Date: 2022-01-01 10:29:43.380762 OrderNum: 27227 CustID: 1'
'ID: 1 Date: 2022-02-02 10:29:43.380762 OrderNum: 27227 CustID: 1'
Enter fullscreen mode Exit fullscreen mode

Get orders filtering by a after a specific date.

>>> after_date = datetime(year=2022, month=2, day=22)
>>> after_feb_22 = Order.query.filter(Order.order_date >= after_date).all()

>>> for order in after_feb_22:
...     f'ID: {order.id} Date: {order.order_date} OrderNum: {order.order_number} CustID: {order.customer_id}'
... 
'ID: 3 Date: 2022-04-03 10:29:43.380762 OrderNum: 27227 CustID: 1'
Enter fullscreen mode Exit fullscreen mode

Get the date and time Cheuk placed the orders.

>>> cheuk_all_orders = Order.query.filter(Order.customer_id == 4).all()

>>> for order in cheuk_all_orders:
...     f'Order date: {order.order_date.strftime("%Y-%m-%d %H:%M")}'
... 
'Order date: 2022-02-07 19:52'
'Order date: 2022-02-07 21:38'

>>> exit()
Enter fullscreen mode Exit fullscreen mode

How To Troubleshoot If You Have Issues

  1. Check carefully for syntax errors.

  2. If you ran db.create_all() with errors in the application.py file you might have to delete the database and recreate it.

$ sudo su – postgres

postgres=# DROP DATABASE mystoredb;
DROP DATABASE

postgres=# CREATE DATABASE mystoredb;
CREATE DATABASE
Enter fullscreen mode Exit fullscreen mode

Then run db.create_all() again to create the tables.

>>> python3

>>> from application import db, Customer, Order

>>> db.create_all()
Enter fullscreen mode Exit fullscreen mode

Feel free to leave comments, questions, and suggestions.

Discussion (4)

Collapse
ekck profile image
Emmanuel Kiprono

hey , am getting this error TypeError: init() missing 1 required positional argument: 'customer_email' when i try to enter customer name

Collapse
brandonwallace profile image
brandon_wallace Author • Edited on

You can add the customer_email when you add the customer to the database.

brandon = Customer(customer_name='Brandon', customer_email='brandon@example.com')
Enter fullscreen mode Exit fullscreen mode
Collapse
ekck profile image
Emmanuel Kiprono

Thank you Brandon, it worked out

Collapse
brandonwallace profile image
brandon_wallace Author

Hello Emmanuel, please try to go over your code and look for any typos. I have tested the code a few times. It works.