# What I Learned Letting a Generative AI Agent Design Our SQL Schema from Scratch

Curious if an LLM can actually design a relational schema that works beyond a toy blog app? Me too. So I handed over the keys to a generative AI agent—gave it just a business description and asked for a SQL schema from scratch. The results? Surprising, sometimes frustrating, and ultimately educational. If you’ve ever wondered what it’s like to have a language model do your database modeling homework for you (and maybe where things go sideways), read on.

## How I Framed the Problem for the AI

To make this a proper test, I didn’t cherry-pick an easy example. I gave the AI a real spec from a side project: "Design a database for a simple event ticketing platform. Users can create events, sell tickets, and buyers can purchase tickets for different events. Track payments, and allow event organizers to see their sales." Pretty standard 3NF stuff, nothing outrageously complex, but enough relationships to trip up a naive approach.

I dumped this prompt into ChatGPT (GPT-4, since that's what I had), and asked for:

- Table definitions with primary/foreign keys
- Brief explanations of choices

## The Schema the AI Gave Me

Here’s a condensed version of what the AI returned (no edits):

```sql
-- Users table: stores both buyers and organizers
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

-- Events table: each event belongs to a user (the organizer)
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    organizer_id INTEGER REFERENCES users(id),
    title VARCHAR(200) NOT NULL,
    event_date DATE NOT NULL
);

-- Tickets table: each ticket is for an event
CREATE TABLE tickets (
    id SERIAL PRIMARY KEY,
    event_id INTEGER REFERENCES events(id),
    price DECIMAL(10,2) NOT NULL,
    seat VARCHAR(50)
);

-- Purchases table: records ticket purchases by users
CREATE TABLE purchases (
    id SERIAL PRIMARY KEY,
    buyer_id INTEGER REFERENCES users(id),
    ticket_id INTEGER REFERENCES tickets(id),
    purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Payments table: tracks payments for purchases
CREATE TABLE payments (
    id SERIAL PRIMARY KEY,
    purchase_id INTEGER REFERENCES purchases(id),
    amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL
);
```

Honestly? Not too bad for a first pass. The AI covered all the basics, and the foreign keys look reasonable. But as I started poking around, some issues popped up.

---

## Where the AI Surprised Me (And Where It Didn’t)

### 1. Table Structure and Normalization

The agent did a solid job keeping things normalized—no redundant user or event info. It also correctly used foreign keys to wire up relationships. This would absolutely pass a database 101 exam.

But what about edge cases? For example, do we need a separate table for organizers, or is a role field enough? Should a ticket be unique to a purchase, or can multiple users buy the same ticket? This stuff gets nuanced fast.

I noticed that in the AI’s schema, every ticket is a single seat or entry. That’s OK for assigned seating, but what about general admission? Turns out this is a classic ambiguity when you’re only given a spec and no follow-up Q&A.

### 2. Running the Schema: A Practical Test

To test its output, I fired up PostgreSQL and ran the schema as-is. It worked, but I immediately hit a snag when trying to model multiple tickets of the same type (say, 100 general admission tickets). In this schema, I’d need to create 100 `tickets` rows, one per seat.

Here's an improved example that tweaks the AI’s schema to handle general admission:

```sql
-- Each event can have different types of tickets (General, VIP, etc.)
CREATE TABLE ticket_types (
    id SERIAL PRIMARY KEY,
    event_id INTEGER REFERENCES events(id),
    name VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    total_available INTEGER NOT NULL
);

-- Each purchase can contain multiple tickets of a type
CREATE TABLE ticket_orders (
    id SERIAL PRIMARY KEY,
    purchase_id INTEGER REFERENCES purchases(id),
    ticket_type_id INTEGER REFERENCES ticket_types(id),
    quantity INTEGER NOT NULL
);
```

*Comments in code:*
- `ticket_types` allows for, say, "VIP" and "General" for an event, each with their own price and availability.
- `ticket_orders` links purchases to the types of tickets and quantities, so a buyer can purchase, for instance, 3 general and 2 VIP tickets in a single purchase.

This is a pretty standard improvement, and you’d expect it from an experienced dev. The AI schema was "correct," but not flexible enough for a real-world scenario.

### 3. Modeling Payments: Real-World Constraints

Another thing I ran into was payment tracking. The AI’s schema simply links `payments` to `purchases`, but what about refunds, partial payments, or payment failures? In production, you almost always need a richer payments model.

Here's a more extensible version:

```sql
-- Payments can be in various states and can relate to refunds
CREATE TABLE payments (
    id SERIAL PRIMARY KEY,
    purchase_id INTEGER REFERENCES purchases(id),
    amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL, -- e.g., 'pending', 'completed', 'failed', 'refunded'
    payment_method VARCHAR(30),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Refunds explicitly relate to payments
CREATE TABLE refunds (
    id SERIAL PRIMARY KEY,
    payment_id INTEGER REFERENCES payments(id),
    amount DECIMAL(10,2) NOT NULL,
    reason VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

*Comments in code:*
- `status` field on `payments` lets you handle more than just "paid" or "unpaid."
- `refunds` is its own table, so you can track partial refunds and reasons.
- `payment_method` (e.g., "credit_card", "paypal") helps with reporting.

This is one of those details that’s easy to gloss over—until your PM asks for a refunds report and you realize your schema can’t answer the question.

### 4. Documentation and Naming: The Devil’s in the Details

One thing I appreciated: the AI gave nice, readable table and column names, and added short comments in the explanations. But as a team, we care about conventions and migrations. The AI didn’t offer a migration plan, nor any versioning strategy for schema changes. That’s not a knock—it’s just an area where real-world experience matters.

---

## Common Mistakes When Using AI for Schema Design

### 1. Overfitting to the Prompt

The AI does exactly what you ask, but not what you mean. If your prompt misses critical business logic (like "can buyers buy multiple tickets at once?"), the schema won't handle it. Always review its output like you would a junior dev’s PR—ask what’s missing.

### 2. Missing Edge Cases

Refunds, failed payments, data audits, soft deletes—these often get skipped. The AI usually nails the happy path, but real systems live in the unhappy paths. I spent a weekend debugging soft-delete logic because the AI never included a `deleted_at` column for recoverable deletes.

### 3. Weak Handling of Roles and Permissions

Most AI schemas lump all users into one table with no way to distinguish roles. In our case, we eventually added a `role` column to the `users` table and custom logic to enforce permissions in code. If your app needs RBAC (role-based access control), don’t expect the AI to get it perfect out of the box.

---

## Key Takeaways

- **AI tools are great for fast prototyping** but you still need to sanity-check (and often refactor) their output for real-world flexibility.
- **The quality of your prompt is everything**—if you miss requirements, so will the model.
- **Edge cases and business rules almost always need human review**; the AI won’t magically know your refund, audit, or permission requirements.
- **Getting a working schema is just the start**—you still need to think through migrations, naming conventions, and long-term maintainability.
- **Don’t blindly trust AI output**; treat it like a junior’s first draft, not a senior architect’s design.

---

## Wrapping Up

Handing schema design to an AI felt like giving a junior dev their first big assignment: they get the basics right, but you’re going to spend some time reviewing and tightening things up. The experience honestly made me appreciate how much of good database design is about asking the right questions up front—not just knowing SQL syntax.

If you’re experimenting with AI-generated schemas, treat them as a solid starter kit and bring your own skepticism. And if you find a generative agent that nails edge cases and business rules on the first try, I definitely want to hear about it.

---

*If you found this helpful, check out more programming tutorials on [our blog](https://pythonassignmenthelp.com/blog). We cover [Python](https://pythonassignmenthelp.com/programming-help/python), [JavaScript](https://pythonassignmenthelp.com/programming-help/javascript), [Java](https://pythonassignmenthelp.com/programming-help/java), [Data Science](https://pythonassignmenthelp.com/programming-help/data-science), and [more](https://pythonassignmenthelp.com/programming-help/database).*