DEV Community

loading...
Cover image for One-To-Many Database Relationships Complete How To

One-To-Many Database Relationships Complete How To

brandonwallace profile image brandon_wallace Updated on ・6 min read

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.6+
  • 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 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 13.1 (Debian 13.1-1.pgdg100+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 python-dotenv
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', 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'))

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

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'] = 'postgres://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', 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

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

Example of what I am using in this tutorial:

'postgres://postgres:123456@127.0.0.1:5432/mystoredb'

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)
>>> maria = Customer(customer_name=Maria)
>>> ahmed = Customer(customer_name=Ahmed)
>>> cheuk = Customer(customer_name=Cheuk)
Enter fullscreen mode Exit fullscreen mode

Add each person to the database session.

>>> db.session.add(brandon)
>>> db.session.add(maria)
>>> db.session.add(ahmed)
>>> db.session.add(cheuk)
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)
Enter fullscreen mode Exit fullscreen mode

Add each order to the session.

>>> db.session.add(order1)
>>> db.session.add(order2)
>>> db.session.add(order3)
>>> db.session.add(order4)
>>> db.session.add(order5)
>>> db.session.add(order6)
>>> db.session.add(order7)
>>> db.session.add(order8)
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 customer in customers:
...     customer
... 
<CustomerID: 1 Brandon>
<CustomerID: 2 Maria>
<CustomerID: 3 Ahmed>
<CustomerID: 4 Cheuk>
Enter fullscreen mode Exit fullscreen mode

Extract the name like this.

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

Get the total order count for Brandon.

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

>>> fTotal 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 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: 2021-02-07 19:52'
'Order date: 2021-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 (0)

Forem Open with the Forem app