Categories, products, customers, orders, reviews, coupons and cart tables — with keys, relationships and indexing
An online clothing store runs on structured information: what products are for sale, how they are grouped into categories, who the customers are, what they put in a cart, what they order, and what they think of what they bought. A database schema is the plan that organizes all of that information into tables, each table holding one kind of record, each record identified by its own primary key, and related records connected by foreign keys. The schema on this page is a basic, working starting point for a clothing store: eight tables covering the catalog, the customers, the buying process and two extras shoppers expect — product reviews and discount coupons. It can stand as the foundation of a small store and can be expanded with shipping, payment, product attributes and user authentication as a store's requirements grow.

Designing a database schema for an online clothing store involves creating tables to store information about products, categories, customers, orders, and more. Here's a basic example of a database schema for such a store:
Table: Categories
Table: Products
Table: Customers
Table: Orders
Table: OrderItems
Table: Reviews
Table: Coupons
Table: CartItems (for tracking items in a customer's shopping cart)
This schema covers the basic functionality of an online clothing store. You can expand it to include more features, such as shipping information, payment processing, product attributes (e.g., size, color), and user authentication, depending on the specific requirements of your application.
Make sure to establish appropriate relationships between tables using foreign keys to maintain data integrity. Additionally, consider indexing fields like ProductID and CustomerID for faster query performance as your database grows.
This page was aided by AI technology from Chat GPT. Learn more about fashion industry artificial intelligence technology.
Learn more about clothing stores.
Each table in this schema stores one kind of record, and the connections between tables are what make the store work. A product belongs to a category, so the Products table carries a CategoryID foreign key that points back to the Categories table. A customer places an order, so Orders carries a CustomerID foreign key. A single order usually contains more than one garment, so the OrderItems table sits between Orders and Products: each OrderItems row links one OrderID to one ProductID, and records the Quantity bought and the Subtotal for that line. That one linking table is what lets an order hold a shirt, a pair of jeans and a jacket at once, each at its own quantity and price, while the Orders table itself keeps only the OrderDate, the TotalAmount and the Status of the whole order — for example pending, shipped or delivered.
The same pattern repeats across the rest of the schema. Reviews carries two foreign keys, ProductID and CustomerID, because a review is always about one product and written by one customer, and its Rating and Comment fields hold what the customer said. CartItems also carries CustomerID and ProductID, because a cart item only makes sense as a particular product set aside by a particular customer, with a Quantity and an AddedAt timestamp showing when it was put there. Coupons stands apart from the order flow: a coupon is identified by its Code, carries a DiscountPercentage, and is bounded by ValidFrom and ValidTo dates so a promotion can start and end on schedule.
The catalog side of the schema begins with Categories, the simplest table here: a CategoryID primary key, a CategoryName and a Description. Categories keep a clothing catalog browsable — dresses, denim, outerwear and shoes can each be a category, and every product is filed under one of them through its CategoryID. The Products table is the heart of the catalog. Alongside its ProductID primary key and CategoryID foreign key, it holds the ProductName and Description a shopper reads, the Price, the StockQuantity that tells the store how many units are on hand, and an ImageURL pointing to the product's picture. CreatedAt and UpdatedAt timestamps, which appear in most of the tables, record when a row was first created and when it was last changed — useful when prices are revised, descriptions are rewritten or stock is corrected.
Clothing has a complication that books and electronics do not: the same garment is sold in several sizes and colors. The basic Products table describes the garment itself, and the schema notes that it can be expanded to include product attributes such as size and color. In practice that expansion means recording the attribute values a shopper chooses — the size picked and the color picked belong with the item as it moves from the product page into CartItems and then into OrderItems, so the store fulfills exactly the variant that was ordered, not just the garment in general. Planning for attributes early matters, because size and color affect stock as well: ten units of a shirt in one size is a different inventory position from ten units spread across five sizes.
The Customers table holds the people side of the store: CustomerID, FirstName, LastName, Email, Address and PhoneNumber, plus CreatedAt and UpdatedAt. Its most important field for security is Password, which is stored hashed — the stored value is the output of a hash function applied to the password, not the password itself in readable form. When a customer signs in, the store can check the password they type against the hashed value without keeping a readable copy of the password in the database. The schema also notes that the design can be expanded for user authentication, the broader set of sign-in and account-access features that build on that customer record.
The buying process itself runs through three tables in sequence. CartItems tracks the items in a customer's shopping cart before checkout, each with its quantity and the time it was added. At checkout, the cart becomes an Orders row — one customer, one order date, one total amount, and a status that follows the parcel from pending through shipped to delivered — and each purchased item becomes its own OrderItems row with quantity and subtotal. Because both cart and order items link back to Products, the store can answer the everyday questions of retail from the schema alone: what is in this customer's cart, what did this order contain, and how much of this product has been sold.
Two pieces of housekeeping keep this schema reliable as data accumulates. The first is data integrity through foreign keys: appropriate relationships between tables, established with foreign keys, keep records consistent — an order item cannot point to a product that does not exist, a review cannot attach to a customer who was never registered, and a product cannot be filed under a category that is not in the Categories table. The keys are what stop the database from filling up with orphaned rows that reference nothing. The second is query speed. Indexing fields such as ProductID and CustomerID gives the database a fast lookup path for the joins and searches a store runs constantly — finding every order for a customer, or every review for a product — and it matters more as the database grows, because a lookup that is instant with a hundred products can become slow with a hundred thousand unless the fields being searched are indexed.
Beyond the eight tables, the growth path is already sketched in the design. Shipping information can be added so an order records where it is going and how it is being sent. Payment processing can be added so the store records how an order was paid for. Product attributes such as size and color can be formalized so variants are tracked in their own right. User authentication can be expanded around the Customers table. None of those additions requires abandoning the starting schema — each one attaches to the tables already here, which is exactly what a basic schema is for: cover the essential functionality of an online clothing store today, in a structure that can absorb the next requirement when it arrives.
The basic schema uses eight tables. Categories (CategoryID, CategoryName, Description) and Products (ProductID, CategoryID, ProductName, Description, Price, StockQuantity, ImageURL) hold the catalog. Customers holds the account record, including a hashed password. Orders and OrderItems record purchases and the individual products in each purchase. Reviews holds a rating and comment for a product from a customer, Coupons holds a code, a discount percentage and validity dates, and CartItems tracks the items in a customer's shopping cart.
Foreign keys establish the relationships between tables — Products to Categories, Orders to Customers, OrderItems to both Orders and Products, and Reviews and CartItems to their product and customer — which maintains data integrity, so records cannot point to products, categories or customers that do not exist. Indexing fields such as ProductID and CustomerID then keeps queries fast as the database grows, because the fields used to join and search the tables can be looked up directly instead of scanning every row.
The basic Products table describes each garment, and the schema can be expanded to include product attributes such as size and color, so the specific variant a shopper chooses can follow the item through the cart and into the order. Customer passwords are never kept as plain readable text in the Customers table: the Password field is stored hashed, so the database holds a hashed value that a sign-in attempt can be checked against.