SQL Flower Shop Database Design

Understanding the Problem

We need to create a database for a flower shop that tracks bouquets and their flowers. Each bouquet has:

• A name
• A price
• One or more flowers

Each flower has:

• A color (optional)
• A stem length (short, medium, long)
• A variety (Rose, Carnation, etc.)

Devising a Plan

1. Create tables (will need a bouquets table, flowers table, and a join table)
2. Insert sample data for two bouquets and their flowers
3. Write query to find bouquets in price range
4. Write query to find flowers by color
5. Write query to find first bouquet with specific flower variety
6. Write delete statement to remove bouquet and its flowers

Solution

Step 1: Create Tables

We'll create three tables with appropriate foreign key relationships:

-- sql-files/01-create-tables.js
CREATE TABLE bouquets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

CREATE TABLE flower_varieties (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    variety VARCHAR(100) NOT NULL
);

CREATE TABLE flowers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    color VARCHAR(50),
    stem_length VARCHAR(20) NOT NULL,
    bouquet_id INTEGER NOT NULL,
    variety_id INTEGER NOT NULL,
    FOREIGN KEY (bouquet_id) REFERENCES bouquets(id) ON DELETE CASCADE,
    FOREIGN KEY (variety_id) REFERENCES flower_varieties(id)
);
    

Step 2: Insert Data

-- sql-files/02-insert-data.js
INSERT INTO bouquets (name, price) VALUES
('Get Well Soon', 59.99),
('Mother''s Day', 39.99);

INSERT INTO flower_varieties (variety) VALUES
('Carnation'),
('Lily'),
('Solidago'),
('Daisy'),
('Baby''s Breath');

-- Get Well Soon bouquet flowers
INSERT INTO flowers (color, stem_length, bouquet_id, variety_id)
SELECT 'yellow', 'short', b.id, v.id
FROM bouquets b, flower_varieties v
WHERE b.name = 'Get Well Soon' AND v.variety = 'Carnation';

-- Insert remaining flowers...
    

Step 3: Find Bouquets by Price Range

-- sql-files/03-find-bouquets-by-price.js
SELECT name, price
FROM bouquets
WHERE price BETWEEN 30.45 AND 40.36
ORDER BY name;
    

Step 4: Find Flowers by Color

-- sql-files/04-find-flowers-by-color.js
SELECT v.variety, f.color, f.stem_length
FROM flowers f
JOIN flower_varieties v ON f.variety_id = v.id
WHERE f.color IN ('pink', 'purple')
ORDER BY v.variety, f.color, f.stem_length;
    

Step 5: Find Bouquet by Flower Variety

-- sql-files/05-find-a-bouquet-by-flower-variety.js
SELECT DISTINCT b.name, b.price
FROM bouquets b
JOIN flowers f ON b.id = f.bouquet_id
JOIN flower_varieties v ON f.variety_id = v.id
WHERE v.variety = 'Carnation'
ORDER BY b.name
LIMIT 1;
    

Step 6: Delete Bouquet

-- sql-files/06-delete-bouquet-by-name.js
DELETE FROM bouquets
WHERE name = 'Get Well Soon';
    

Code Explanation

Let's break down each part of the solution:

Database Schema Design

We used three tables to represent our data:

• bouquets: Stores bouquet information (name, price)
• flower_varieties: Stores flower types (Carnation, Rose, etc.)
• flowers: Links flowers to bouquets with color and stem length

This design follows database normalization principles:

• Each table has a clear primary purpose
• We avoid storing redundant data
• Relationships are maintained through foreign keys

Real World Application

This database design pattern is similar to many real-world scenarios:

• Restaurant menus (meals containing ingredients)
• Shopping carts (orders containing products)
• Music playlists (playlists containing songs)

Looking Back

Our solution successfully handles all the required operations:

• Finding bouquets in a price range
• Finding flowers by color
• Finding bouquets containing specific flowers
• Deleting bouquets and their associated flowers

The ON DELETE CASCADE in our foreign key ensures that when we delete a bouquet, all its associated flowers are automatically deleted as well.

Advanced Concepts

Some advanced SQL concepts used in this solution:

• JOIN operations to combine data from multiple tables
• Subqueries for complex data insertion
• Foreign key constraints with cascading deletes
• Indexing (implicit through primary keys)