AI Dev Tips 1: Business Idea to PostgreSQL Database Schema in 5 Minutes

Transform a business idea into a PostgreSQL database schema in just five minutes, including tables, attributes, and types,

Why It’s Useful:

• Rapid Prototyping: Quickly create database schemas to test and validate business ideas. Schemas can be used to automatically create your app’s database and tables.
• Enhanced Productivity: Leverage AI to reduce manual effort and increase efficiency.
• Improved Accuracy: Generate precise database structures that align with business requirements.
• Better Planning: Use AI-generated schemas as a foundation for detailed database design.
• Flexibility: Easily adapt schemas based on evolving business needs and scenarios.

Use Cases:

• Startup MVP Development: Quickly generate database schemas to prototype and validate MVPs.
• Feature Expansion: Update database schemas when adding new features to existing applications.
• Learning Tool: Use AI-generated schemas as examples for educational purposes or training.
• Scenario Analysis: Test different business scenarios by modifying schema structures.
• Database Migration: Use generated schemas as a reference when migrating from other database systems.

Define Schema Requirements

A PostgreSQL schema is like a template or a logical specification for a database that sets the format of database objects such as tables, views, indexes, and functions.

Basically, like a map of the major parts of your database.

From a schema — you can create a database, tables, attributes using either online database tools, CLI (command line) or a small amount of code.

Therefore, it’s also like a “template” to speed up deployments, prototyping, backups, experiments and more.

btw, quick promo — check out the ebook I wrote detailing 190+ cloud metrics for professional cloud engineers in the Cloud Metrics guide. It’s 800+ pages packed with info. ebook.cloudmetricsguide.com

It helps in planning and to avoid naming conflicts and provides a way to group related objects together, making database management more efficient. Schemas also facilitate database security and access control by allowing administrators to set permissions at the schema level.

NOTE: This is for prototyping only. Obviously on a production level you need to fine-tune and work more on this. You have many more considerations, constraints etc. But it works. This is a first draft! Not a FINAL draft!

ChatGPT: https://chat.openai.com/

ChatGPT 4o Prompt:

“I need to create a PostgreSQL schema for an online bookstore. The key feature is that users should be able to search for and purchase books. Please outline the tables, attributes, types, and keys needed for this schema. Give 7 tables and attributes for each:”

ChatGPT output:

PostgreSQL Schema for an Online Bookstore

1. Users

• user_id: SERIAL PRIMARY KEY
• username: VARCHAR(50) UNIQUE NOT NULL
• email: VARCHAR(100) UNIQUE NOT NULL
• password_hash: VARCHAR(255) NOT NULL
• first_name: VARCHAR(50)
• last_name: VARCHAR(50)
• created_at: TIMESTAMP DEFAULT CURRENT_TIMESTAMP

2. Books

• book_id: SERIAL PRIMARY KEY
• title: VARCHAR(255) NOT NULL
• author: VARCHAR(255) NOT NULL
• genre: VARCHAR(100)
• price: NUMERIC(10, 2) NOT NULL
• published_date: DATE
• stock_quantity: INTEGER NOT NULL

3. Orders

• order_id: SERIAL PRIMARY KEY
• user_id: INTEGER REFERENCES Users(user_id)
• order_date: TIMESTAMP DEFAULT CURRENT_TIMESTAMP
• total_amount: NUMERIC(10, 2) NOT NULL
• status: VARCHAR(50) DEFAULT ‘Pending’
• shipping_address: VARCHAR(255) NOT NULL
• billing_address: VARCHAR(255) NOT NULL

4. OrderItems

• order_item_id: SERIAL PRIMARY KEY
• order_id: INTEGER REFERENCES Orders(order_id)
• book_id: INTEGER REFERENCES Books(book_id)
• quantity: INTEGER NOT NULL
• price_per_unit: NUMERIC(10, 2) NOT NULL
• total_price: NUMERIC(10, 2) GENERATED ALWAYS AS (quantity * price_per_unit) STORED

5. Reviews

• review_id: SERIAL PRIMARY KEY
• book_id: INTEGER REFERENCES Books(book_id)
• user_id: INTEGER REFERENCES Users(user_id)
• rating: INTEGER CHECK (rating >= 1 AND rating <= 5)
• review_text: TEXT
• review_date: TIMESTAMP DEFAULT CURRENT_TIMESTAMP

6. Authors

• author_id: SERIAL PRIMARY KEY
• name: VARCHAR(255) NOT NULL
• biography: TEXT
• date_of_birth: DATE
• nationality: VARCHAR(100)

7. Genres

• genre_id: SERIAL PRIMARY KEY
• genre_name: VARCHAR(100) UNIQUE NOT NULL
• description: TEXT

Notes:

• Foreign Keys are used to establish relationships between tables (e.g., user_id in Orders references Users, book_id in OrderItems references Books).
• Constraints such as UNIQUE, NOT NULL, and CHECK are used to ensure data integrity.
• SERIAL data type is used for auto-incrementing primary keys.

That’s what one prompt produced.

All of that.

ChatGPT prompt:

Prompt: “Generate a PostgreSQL schema as code with the following tables and attributes: [in here… use the what was output and listed above in the previous response]

Output

-- Schema for an online bookstore

CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    genre VARCHAR(100),
    price NUMERIC(10, 2) NOT NULL,
    published_date DATE,
    stock_quantity INTEGER NOT NULL
);

CREATE TABLE Orders (
    order_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES Users(user_id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount NUMERIC(10, 2) NOT NULL,
    status VARCHAR(50) DEFAULT 'Pending',
    shipping_address VARCHAR(255) NOT NULL,
    billing_address VARCHAR(255) NOT NULL
);

CREATE TABLE OrderItems (
    order_item_id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES Orders(order_id),
    book_id INTEGER REFERENCES Books(book_id),
    quantity INTEGER NOT NULL,
    price_per_unit NUMERIC(10, 2) NOT NULL,
    total_price NUMERIC(10, 2) GENERATED ALWAYS AS (quantity * price_per_unit) STORED
);

CREATE TABLE Reviews (
    review_id SERIAL PRIMARY KEY,
    book_id INTEGER REFERENCES Books(book_id),
    user_id INTEGER REFERENCES Users(user_id),
    rating INTEGER CHECK (rating >= 1 AND rating <= 5),
    review_text TEXT,
    review_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Authors (
    author_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    biography TEXT,
    date_of_birth DATE,
    nationality VARCHAR(100)
);

CREATE TABLE Genres (
    genre_id SERIAL PRIMARY KEY,
    genre_name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT
);

At this point you can use a variety of tools to create a Postgres database and tables from the Postgres schema you now have.

btw, quick promo — check out the ebook I wrote detailing 190+ cloud metrics for professional cloud engineers in the Cloud Metrics guide. It’s 800+ pages packed with info. ebook.cloudmetricsguide.com

For this example I will use pg-sql which is a basic but capable online postgres tool that does not require downloading anything or a lot of configuration… it;s perfect to illustarte how to use this.

pg-sql: https://pg-sql.com/

Go there and you will see something like this:

Click on the Schema button and you will see this, because you have not put in a schema yet:

Exit from that.

Go back to the empty input text box for “Enter Query” put in the output of the code from the ChaGPT prompt:

Now, click the > Run button — this execute the insert of the schema.

You will see at the bottom of the page “CREATE successful”:

Now click the Schema button again and we have:

Confirm also by looking the query history:

That shows the query was run.

And the results again shows:

So now you have a schema that you can give to any developer.

If you run the exact same query a second time:

This error is because you already have this schema and tables in there already.

You can hit the Reset DB button to empty the whole database back to nothing.

Of course we just did a very simple one.

You could easily do a more complicated one and fine tune it with only the attributes you want.

When you do your prompt you can provide various table names and attributes and this is going to change the schema provided.

If you are into Cloud Engineering… I wrote a guide to cloud metrics.

Store: Buy the full Cloud Metrics book / more info: CloudMetricsGuide.com

Cloud metrics covered:

• 190+ metrics in the categories of: User, Network, Reliability, Compute, Compute Scaling, API, Database, Storage, Events and Queues, Security and Cost metrics.
• 190 + cloud metrics in detail with insights!
• 800+ pages of valuable content, the ULTIMATE reference guide!
• PDF, Searchable, Linked Table of Contents (TOC) for fast navigation.
• Scenario/s giving example calculations, formulas and realistic situations.
• List of “Gotchas” for each metric usage, for better understanding.
• Best Practices and implementation suggestions for each metric!!!

Store: Buy the full ebook / more info: CloudMetricsGuide.com

About me

I’m a cloud architect, senior developer and tech lead who enjoys solving high-value challenges with innovative solutions.

I’m always open to discussing projects. If you need help, have an opportunity or simply want to chat, you can reach me on X/Twitter @csjcode and same username gmail. Also, can schedule a Zoom here (lower on page): https://systemsarchitect.io/docs/consulting

I’ve worked 20+ years in software development, both in an enterprise setting such as NIKE and the original MP3.com, as well as startups like FreshPatents, SystemsArchitect.io, API.cc, and Instantiate.io.

My experience ranges from cloud ecommerce, API design/implementation, serverless, AI integration for development, content management, frontend UI/UX architecture and login/authentication. I give tech talks, tutorials and share documentation of architecting software. Also previously held AWS Solutions Architect certification.

Recently I’m working on Instantiate.io, a value creation experiment tool to help startup planning with AI. I’m making progress writing a reference manual on cloud metrics.

Also, an enthusiast of blockchain, I’m active working on applications in the innovative Solana blockchain ecosystem.

Thanks for reading and hope to see you around!