Python for Profit

Building a Complete Inventory System

14

Managing Relationships: Suppliers

The Big Idea

This chapter introduces one of the most powerful concepts in database design—relationships—by creating a Supplier table and linking it to our existing Product table using a Foreign Key, allowing our application to track which supplier provides which product.

Roadmap

  • Beyond a Simple List: We'll discuss why a real inventory system must track not just what you have, but where it came from.

  • The Power of Relationships: Foreign Keys: Introducing the concept of a ForeignKeyField, the tool ORMs use to link one table to another.

  • System Design: An Updated ERD: We'll visualize the new relationship between our Products and Suppliers tables.

  • Expanding main.py Step-by-Step:

1.  Create a new `Supplier` model class.

2.  Modify the `Product` model to add the `ForeignKeyField`.

3.  Update the database initialization to create both tables.

4.  Create a new set of CRUD functions for managing suppliers.

5.  Upgrade `add_product` to allow selecting a supplier.

6.  Upgrade `view_products` to show supplier information using a database `JOIN`.

7.  Expand the main menu to include supplier management options.

Full Chapter Content

Why We Need to Track Suppliers

Our application can manage products perfectly, but in the real world, a business owner needs to answer questions like:

  • "Who do I call to reorder more laptops?"

  • "Which products do I get from 'Global Tech Imports'?"

  • "Show me all the suppliers I work with."

To answer these, we need to store information about our suppliers and, crucially, create a relationship between a supplier and the products they provide. This is where the true power of a relational database shines.

Foreign Keys: The Link Between Tables

We will create a new Supplier table. How do we link a Product to a Supplier? We add a special column to the Product table called a Foreign Key.

A Foreign Key in one table is a column that holds the Primary Key of a record from another table. In our case, the Product table will have a supplier_id column, which will store the unique ID of the relevant supplier from the Supplier table.

Peewee makes creating this link incredibly simple with its ForeignKeyField.

System Design: Our New ERD

Let's update our blueprint to visualize this new relationship. The line with the "crow's foot" indicates a "one-to-many" relationship: one supplier can have many products.

+-----------------------------+      +-----------------------------+
|          Suppliers          |      |          Products           |
|-----------------------------|      |-----------------------------|
| PK | id         | INTEGER   |      | PK | id         | INTEGER   |
|    | name       | TEXT      |      |    | name       | TEXT      |
|    | contact    | TEXT      |      |    | quantity   | INTEGER   |
+-----------------------------+      |    | price      | REAL      |
       |                         FK  |    | supplier_id| INTEGER   |
       |                             +-----------------------------+
       +--------------------------------|

Let's implement this in our code.

Step 1: Create the Supplier Model

First, we need to define our new Supplier table as a Peewee model, just like we did for Product.

In main.py, add this new class definition right below your Product model:

class Supplier(Model):
    name = CharField(unique=True)
    contact_info = TextField() # TextField is for longer, multi-line text

    class Meta:
        database = db

Explanation:

  • We've created a Supplier model with a unique name and a contact_info field.

  • We're using TextField, which is better suited for potentially longer content like an address or phone number compared to CharField.

Step 2: Add the Foreign Key to the Product Model

Now, we need to add the link to our Product model.

Modify your existing Product class to add the new ForeignKeyField:

class Product(Model):
    name = CharField(unique=True)
    quantity = IntegerField()
    price = FloatField()
    # This is the new relationship field
    supplier = ForeignKeyField(Supplier, backref='products')

    class Meta:
        database = db

Explanation of What's New:

  • supplier = ForeignKeyField(Supplier, ...): This single line does all the magic. It tells Peewee to add a supplier_id column to the product table.

  • backref='products': This is a powerful Peewee feature. It creates a "virtual" field on the Supplier model. It means we can get all products for a given supplier simply by writing some_supplier.products. Peewee will handle the database query for us automatically.

Step 3: Update the Database Initialization

We need to tell Peewee to create our new Supplier table when the application starts.

Find the if __name__ == "__main__": block at the bottom of your file and modify the create_tables line:

# --- Find this line in your Main Application Block ---
# Old way: db.create_tables([Product])

# New way, creating both tables:
db.create_tables([Product, Supplier])

Before you run the next time: You must delete your old inventory.db file. We have changed the structure of our tables, so we need to start with a fresh database.

Step 4: Create CRUD Functions for Suppliers

A user needs to be able to add, view, and manage suppliers. Let's create a new set of CRUD functions for them. This process is very similar to what we did for products.

Add this new block of functions to your main.py, right below your product CRUD functions:

# --- Supplier CRUD Functions ---

def add_supplier(name, contact_info):
    """Adds a new supplier to the database."""
    try:
        Supplier.create(name=name, contact_info=contact_info)
        print(f"SUCCESS: Added supplier '{name}'.")
    except IntegrityError:
        print(f"ERROR: A supplier with the name '{name}' already exists.")

def view_suppliers():
    """Retrieves and displays all suppliers."""
    suppliers = Supplier.select()
    print("\n--- All Suppliers ---")
    if not suppliers:
        print("No suppliers found.")
    else:
        for supplier in suppliers:
            print(f"ID: {supplier.id}, Name: {supplier.name}, Contact: {supplier.contact_info}")
    print("---------------------")

Step 5: Upgrade the add_product and view_products Functions

Now we connect everything. When a user adds a product, they need to select a supplier. When they view products, we should see the supplier's name.

First, let's modify add_product. It's going to become more interactive.

# --- Replace your old add_product function with this one ---
def add_product(name, quantity, price):
    """Adds a new product, associating it with a supplier."""
    view_suppliers() # Show the user the available suppliers
    supplier_id = int(input("Please enter the ID of the supplier for this product: "))

    try:
        supplier = Supplier.get(Supplier.id == supplier_id)
        Product.create(name=name, quantity=quantity, price=price, supplier=supplier)
        print(f"SUCCESS: Added '{name}' from supplier '{supplier.name}'.")
    except DoesNotExist:
        print("ERROR: That supplier ID does not exist.")
    except IntegrityError:
        print(f"ERROR: A product with the name '{name}' already exists.")

Explanation of Changes:

  • The function now shows the supplier list and asks the user for a supplier's ID.

  • Supplier.get(Supplier.id == supplier_id): This is a new Peewee method. .get() is used when you expect exactly one result. It will raise a DoesNotExist error if no supplier with that ID is found, which we catch.

  • supplier=supplier: When we create the product, we pass the entire supplier object we just retrieved. Peewee understands this relationship and will correctly save the supplier's ID in the foreign key column.

Next, let's modify view_products to show the supplier's name. Peewee makes this incredibly easy.

# --- Replace your old view_products function with this one ---
def view_products():
    """Retrieves and displays all products and their suppliers."""
    # Peewee automatically handles the 'JOIN' to get supplier data
    products = Product.select()

    print("\n--- Current Inventory ---")
    if not products:
        print("The inventory is empty.")
    else:
        for product in products:
            # We can now access the supplier's name directly!
            supplier_name = product.supplier.name
            print(f"ID: {product.id}, Name: {product.name}, Supplier: {supplier_name}, Qty: {product.quantity}, Price: ${product.price:.2f}")
    print("-------------------------")

Explanation of Changes:

  • product.supplier.name: This is the magic of the ORM. Because we defined the ForeignKeyField, Peewee knows product.supplier refers to the linked record in the Supplier table. It automatically and efficiently fetches that data (using a database JOIN behind the scenes) so we can access its attributes.

Step 6: Expand the Main Menu

Finally, let's add the new supplier management options to our main menu loop.

Replace the while True: loop in your if __name__ == "__main__": block with this expanded version:

    while True:
        print("\n--- PyInventory Main Menu ---")
        print("--- Product Management ---")
        print("1: View All Products")
        print("2: Add a New Product")
        print("--- Supplier Management ---")
        print("3: View All Suppliers")
        print("4: Add a New Supplier")
        print("q: Quit")

        choice = input("Please enter your choice: ")

        if choice == '1':
            view_products()
        elif choice == '2':
            name = input("Enter new product name: ")
            quantity = int(input("Enter quantity: "))
            price = float(input("Enter price: "))
            add_product(name, quantity, price)
        elif choice == '3':
            view_suppliers()
        elif choice == '4':
            name = input("Enter new supplier name: ")
            contact = input("Enter supplier contact info: ")
            add_supplier(name, contact)
        elif choice.lower() == 'q':
            print("Exiting PyInventory. Goodbye!")
            break
        else:
            print("Invalid choice. Please try again.")

Chapter 14: Summary & What's Next

You have just implemented the single most important feature of a relational database.

  • You learned how to create a one-to-many relationship between two tables using a ForeignKeyField.

  • You expanded your application to manage a completely new type of data: Suppliers.

  • You saw how an ORM simplifies fetching related data, automatically performing complex JOIN operations behind the scenes.

Our application's logic is becoming truly powerful. We can track what we have and where it comes from. In the next chapter, we'll complete the inventory lifecycle by implementing Purchasing and Sales, allowing us to track the flow of goods into and out of our business.