Practice: A Design Review

Finally, review a design the way a database engineer would. This table was built in a hurry. Find what is wrong, then fix it with the ideas from the course: one value per cell, no repeated facts, keys, constraints.

Problems to spot: the customer's phone and city repeat on every order; several items sit in one text column; nothing stops a negative quantity; and there is no primary key on the orders.

Examplepython

import sqlite3

db = sqlite3.connect(':memory:')

db.execute('CREATE TABLE orders_bad (customer TEXT, phone TEXT, city TEXT, items TEXT, qty INTEGER)')
db.executemany('INSERT INTO orders_bad VALUES (?, ?, ?, ?, ?)', [
    ('Ama', '024', 'Accra', 'rice,oil', 2), ('Ama', '024', 'Accra', 'soap', 1), ('Kofi', '055', 'Kumasi', 'rice', -4)])

# a better design
db.executescript('''
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, phone TEXT, city TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id));
CREATE TABLE order_items (order_id INTEGER NOT NULL REFERENCES orders(id), item TEXT NOT NULL,
                          qty INTEGER NOT NULL CHECK (qty > 0), PRIMARY KEY (order_id, item));
INSERT INTO customers VALUES (1, 'Ama', '024', 'Accra'), (2, 'Kofi', '055', 'Kumasi');
INSERT INTO orders VALUES (1, 1), (2, 1), (3, 2);
INSERT INTO order_items VALUES (1, 'rice', 2), (1, 'oil', 1), (2, 'soap', 1), (3, 'rice', 4);
''')
print(db.execute('''SELECT c.name, COUNT(*) AS lines, SUM(i.qty) AS units FROM customers c
                    JOIN orders o ON o.customer_id = c.id JOIN order_items i ON i.order_id = o.id
                    GROUP BY c.id ORDER BY c.name''').fetchall())

Open in editor »

Quick check

Which change fixes 'rice,oil' stored in one cell?

One row per item in a separate tableA longer text columnUpper-casing the text

Your turn: write the code

Add a CHECK that qty > 0, then try to insert qty -1 and print refused if the database rejects it.

Finish the quick check (and any code exercise) to complete this lesson, or mark it done yourself.

Tip: use the left and right arrow keys to move between lessons.